Sorting a varchar field that is also numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting a varchar field that is also numeric

Author

Topic

ronmc
Starting Member

12 Posts

Posted - 2005-07-28 : 15:12:33

Can any tell me how to do a sort on a field where the datatype is varchar, but the data in the table is alphanumeric?

I looked at BOL, and it gave a really nice dissertation on collation, etc., but I'm hoping that this will be a little easier that I'm making out to be.

Here is my code so far:

select adjcode, reason from adjcodes order by adjcode

Results:

1 Bad Stuff
10 Really Bad Stuff
2 Out of order here

vr8ce
Starting Member

23 Posts

Posted - 2005-07-28 : 16:08:23

quote:
Originally posted by ronmc

Can any tell me how to do a sort on a field where the datatype is varchar, but the data in the table is alphanumeric?

See CAST.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 01:29:21

If adjcode has only numbers then

select adjcode, reason from adjcodes order by adjcode * 1

Otherwise

select adjcode, reason from adjcodes order by len(adjcode),adjcode

But it is better to use the proper datatype

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 04:14:02

"order by len(adjcode),adjcode"

I like that ... maybe lends itself to

order by CASE WHEN IsNumeric(adjcode) THEN len(adjcode) ELSE 99999 END, adjcode

so all the non numerics are at one end - allowing for the vagaries of IsNumeric() of course ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 04:57:19

>>order by CASE WHEN IsNumeric(adjcode) THEN len(adjcode) ELSE 99999 END, adjcode

Thats good point except that IsNumeric(adjcode) should be IsNumeric(adjcode)=1

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 05:34:40

Catches me out every time - I'm used to languages where TRUE is useable logic result :-(

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 05:51:18

No problem. Thats nature

Madhivanan

Failing to plan is Planning to fail

ronmc
Starting Member

12 Posts

Posted - 2005-07-29 : 10:16:17

Thanks everyone for the help! This code works great:

select * from adjcodes
order by CASE WHEN IsNumeric(adjcode)=1 THEN len(adjcode) ELSE 99999 END, adjcode

I really appreciate all your help

Makans
Starting Member

2 Posts

Posted - 2005-08-17 : 09:16:46

quote:
Originally posted by madhivanan

No problem. Thats nature

Madhivanan

Failing to plan is Planning to fail

Hi can You help me? I have same problem: I want to sort column, which type is varchar and data is numbers and also character. I work with Interbase and C++ Builder4.
Hope to get your ansvers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 09:42:07

Well

See if this works

Select columns from yourTable order by len(varcharField), varcharfield

Madhivanan

Failing to plan is Planning to fail

Makans
Starting Member

2 Posts

Posted - 2005-08-17 : 09:47:44

quote:
Originally posted by madhivanan

Well

See if this works

Select columns from yourTable order by len(varcharField), varcharfield

Madhivanan

Failing to plan is Planning to fail

No this do not work!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 10:11:00

Can you post some sample data with the result you want?

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-17 : 11:49:46

"I work with Interbase and C++ Builder4"...this is a SQL Server site. Try www.dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources