Order varchar numericly

Question

Post reply

Order varchar numericly

PHXHoward

SSCrazy Eights

Points: 8389
More actions
May 18, 2011 at 4:42 pm

#251147

Hi, I would like to sort SQL Server versions numericly. How can I do that?
For example, they are stored in a varchar column so they "order by" like this:
10.00.1600
10.00.1763
10.00.1779
10.00.1787
10.00.1798
10.00.1806
10.00.1812
10.00.1818
10.00.1823
9.00.4315
9.00.4317
9.00.4325
9.00.5000
9.00.5254
9.00.5259
9.00.5266
Is there another data type other than varchar that I can use so they "order by" properly or some other way to do it?
Thanks for reading.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Sure, there is a really simple way to handle this and other "numeric stored as char" sorting issues....

ORDER BY LEN(<column>),<column>

The LEN(<column>) sort puts smaller numbers (fewer digits) at the start...

Of course, it works here because the rest of the version number is static length.

If you found it wasn't (or just wanted to be even more careful) you could break the string up and sort by the parts...

;with cte(vers)

as

(

select '10.00.1818'

union all

select '10.00.1823'

union all

select '9.00.4315'

union all

select '9.00.4317'

)

select vers

from cte

cross apply (select part1 = PARSENAME(vers,3), part2 = PARSENAME(vers,2), part3 = PARSENAME(vers,1)) as parts

order by LEN(part1),part1,LEN(part2),part2,LEN(part3),part3

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Thank you MM. This is very useful information.

I appriciate the help.

Howard

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

Some SQL Server versions have three decimal places like 8.00.2055 and some have four decimal places 10.50.1600.1.

Is padding the beginning or end of the short version numbers the best option?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

PHXHoward (5/19/2011)
Some SQL Server versions have three decimal places like 8.00.2055 and some have four decimal places 10.50.1600.1.
Is padding the beginning or end of the short version numbers the best option?

Try this

;with cte(vers)

as

(

select '10.00.1818'

union all

select '10.00.1823'

union all

select '9.00.4315'

union all

select '9.00.4317'

union all

select '10.50.1600.1'

union all

select '10.50.1600'

union all

select '10.50.1600.2'

)

select vers

from cte

cross apply(select REVERSE(vers)) as a(srev)

cross apply (select part1 = PARSENAME(srev,1), part2 = PARSENAME(srev,2), part3 = PARSENAME(srev,3), part4 = PARSENAME(srev,4)) as parts

order by LEN(part1),part1,LEN(part2),part2,LEN(part3),part3,LEN(part4),part4

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 5

PHXHoward

SSCrazy Eights

Points: 8389

May 19, 2011 at 12:28 pm

#1327289

Yes, that worked perfectly. Very impressive MM.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Any special way to make your trick work when there are negatives numbers too?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Ninja's_RGR'us (5/19/2011)
Any special way to make your trick work when there are negatives numbers too?

Never tried it - can't think of one off the top of my head!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

There's the obvious case option.

In my question I'm wondering about a single occurance as the first character... maybe I'm just overthinking this atm!

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 9

Assuming all parsed values are INTs, it's pretty easy:

;with cte(vers)

as

(

select '10.00.1818'

union all

select '10.00.1823'

union all

select '9.00.4315'

union all

select '9.00.4317'

union all

select '10.50.1600.1'

union all

select '10.50.1600'

union all

select '10.50.1600.2'

union all

select '-10.50.1600.2')

select vers

from cte

cross apply(select REVERSE(vers)) as a(srev)

cross apply

(

select

CAST(REVERSE(PARSENAME(srev,1)) AS INT) AS part1,

CAST(REVERSE(PARSENAME(srev,2)) AS INT) AS part2,

CAST(REVERSE(PARSENAME(srev,3)) AS INT) AS part3,

CAST(REVERSE(PARSENAME(srev,4)) AS INT) AS part4

) as parts

order by

part1,

part2,

part3,

part4

Tiya Ten Centuries Points: 1146 More actions · Answer 10

Hi mister.magoo,

Please tell me more about CTE..I am new to it and if possible give with an simple example how does it help and what is use of it?

WayneS SSC Guru Points: 95460 More actions · Answer 11

Hi Tiya,

It's not so much CTE that you need to learn about, as it is WITH common_table_expression (Transact-SQL).

Edit: CTE is just an acronym for "common-table-expression".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 12

Ninja's_RGR'us (5/19/2011)
Any special way to make your trick work when there are negatives numbers too?

use tempdb

go

if(object_id('tempdb..#test') is null) create table #test(ver varchar(50))

truncate table #test

insert into #test(ver)

values

('10.00.1600'),

('10.00.1763'),

('10.00.1779'),

('10.00.1787'),

('10.00.1798'),

('10.00.1806'),

('10.00.1812'),

('10.00.1818'),

('10.00.1823'),

('9.00.4315'),

('9.00.4317'),

('9.00.4325'),

('9.00.5000'),

('9.00.5254'),

('9.00.5259'),

('9.00.5266')

select * from #test

order by cast(replace(ver,'.','')as int)

It will not use an index on the column ver; but it does the trick.

- arjun

https://sqlroadie.com/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

Arjun Sivadasan (5/23/2011)
Ninja's_RGR'us (5/19/2011)
Any special way to make your trick work when there are negatives numbers too?
use tempdb
go
if(object_id('tempdb..#test') is null) create table #test(ver varchar(50))
truncate table #test
insert into #test(ver)
values
('10.00.1600'),
('10.00.1763'),
('10.00.1779'),
('10.00.1787'),
('10.00.1798'),
('10.00.1806'),
('10.00.1812'),
('10.00.1818'),
('10.00.1823'),
('9.00.4315'),
('9.00.4317'),
('9.00.4325'),
('9.00.5000'),
('9.00.5254'),
('9.00.5259'),
('9.00.5266')
select * from #test
order by cast(replace(ver,'.','')as int)
It will not use an index on the column ver; but it does the trick.
- arjun

Well, not quite - what will it do with 9.00.5266.1 in that list? or what about if you get 9.00.523 or similar?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 14

Ya, my bad. My script won't work. I went by the test data.

- arjun

https://sqlroadie.com/