Ordering Interger values stored in Varchar column

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 replicate('0',len(data)),data

--Method 6

select data from @t
order by replicate('0',(select max(len(data+0)) from @t)-len(data))+data

--Method 7

select data from @t
cross join
(
        select len(max(data+0)) as ln from @t
) as t
order by replicate('0',ln-len(data))+data

Published 21 July 2008 14:28 by Madhivanan
Filed under: , ,

Comments

# re: Ordering Interger values stored in Varchar column

23 July 2008 21:27 by PileOfMush

Bored, I decided to test these for speed. My @t was a large set of varchars I obtained by stripping the number 4 out of a convenient set of phone numbers. Methods 1 & 2 were nearly identical, each taking about 6.5 seconds. Method 3 took about 7 seconds. Method 4 took nearly 10 seconds.

And the winner was Method 5 taking only 3 seconds. The most complicated looking code for a human to read can sometimes be the fastest for the server. =-)

# re: Ordering Interger values stored in Varchar column

26 July 2008 11:55 by Madhivanan

Thanks PileOfMush for the testing Smile