Ordering Interger values stored in Varchar column

Published 21 July 08 12:21 PM | Madhivanan 

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?"

Here are some methods

declare @t table(data varchar(15))

insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
select '1290' union all
select '45764'

--Method 1

select data from @t
order by cast(data as int)

--Method 2

select data from @t
order by data+0

--Method 3

select data from @t
order by len(data),data

--Method 4

select data from @t
order by replace(str(data),' ','0')

--Method 5

select data from @t
group by data
order by right(replicate('0',len(max(data))),len(data))

 

Filed under: , ,

Comments

# Other SQL Server Blogs around the Web said on July 21, 2008 7:45 AM:

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?" Here

# SQL Server Transact-SQL (SSQA.net) : Ordering Interger values stored in Varchar column said on July 21, 2008 10:07 AM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/07/21/4636.aspx

# Recent URLs tagged Insert - Urlrecorder said on October 29, 2008 10:46 AM:

PingBack from http://www.urlrecorder.com/insert

Anonymous comments are disabled

Search

Go

This Blog

«July 2008»
SMTWTFS
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

Syndication