How to sort a varchar field with numbers

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

General SQL Server Forums

New to SQL Server Programming

How to sort a varchar field with numbers

Author

Topic

cnbhold
Starting Member

43 Posts

Posted - 2010-07-15 : 13:06:22

I have a varchar field that contains the following data

1.x
2.x
3.x
4.x
5.x
6.x
7.x
8.x
9.x
10.x
11.x
12.x
13.x

I need to write a SQL query that will sort the field in DESC order

13.x
12.x
11.x
10.x
9.x
8.x
7.x
6.x
5.x
4.x
3.x
2.x
1.x

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 13:08:49

Did you try "Order by " <column> desc.

Can you post some sample data and expected output ?

cnbhold
Starting Member

43 Posts

Posted - 2010-07-15 : 13:12:16

Here's my query and output

select
number
from
version
order by
number desc

Query Output

9.x
8.x
7.x
6.x
5.x
4.x
3.x
2.x
12.x
11.x
10.x
1.x

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 13:29:11

Try this:

I am assuming that there will always be a '.' in the field. If this is not the case then let us know so that the solution can be modified.

select number from version
order by cast(left(number, charindex('.',number) -1) as int) desc

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

cnbhold
Starting Member

43 Posts

Posted - 2010-07-15 : 13:35:57

Bohra,

Thanks!!!! That worked...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 23:25:47