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

Anonymous comments are disabled