Archive for octubre 2011
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 @tselect '6134' union allselect '144' union allselect '7345' union allselect '109812' union allselect '100074'union allselect '1290' union allselect '45764'
--Method 1
select data from @torder by cast(data as int)
--Method 2
select data from @torder by data+0
--Method 3
select data from @torder by len(data),data
--Method 4
select data from @torder by replace(str(data),' ','0')
--Method 5
select data from @tgroup by dataorder by replicate('0',len(data)),data
--Method 6
select data from @torder by replicate('0',(select max(len(data+0)) from @t)-len(data))+data
--Method 7 select data from @tcross join
( select len(max(data+0)) as ln from @t) as torder by replicate('0',ln-len(data))+data
Here are some methods
declare @t table(data varchar(15))
insert into @tselect '6134' union allselect '144' union allselect '7345' union allselect '109812' union allselect '100074'union allselect '1290' union allselect '45764'
--Method 1
select data from @torder by cast(data as int)
--Method 2
select data from @torder by data+0
--Method 3
select data from @torder by len(data),data
--Method 4
select data from @torder by replace(str(data),' ','0')
--Method 5
select data from @tgroup by dataorder by replicate('0',len(data)),data
--Method 6
select data from @torder by replicate('0',(select max(len(data+0)) from @t)-len(data))+data
--Method 7 select data from @tcross join
( select len(max(data+0)) as ln from @t) as torder by replicate('0',ln-len(data))+data
