Correct sort character data as numeric

Andrey Alferov — Wed, 28 Nov 2018 10:38:20 +0000

Today I found solve problem to sort data in correct order. I say about char data content numeric data.
For example:

with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData

scol
10.1.1
1.1.1
2.1.1
2.10.1
2.2.1

If I use standard sort

with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData
order by scol

output:

scol
10.1.1
1.1.1
2.10.1
2.1.1
2.2.1

This is not right, but if I use split it's very cood output

with rawData (sCol) as (
select '10.1.1'
union all
select '1.1.1'
union all
select '2.1.1'
union all
select '2.10.1'
union all
select '2.2.1'
)
select * from rawData
order by
split_part(sCol,'.',1)::numeric,
split_part(sCol,'.',2)::numeric,
split_part(sCol,'.',3)::numeric

Output

scol
1.1.1
2.1.1
2.2.1
2.10.1
10.1.1

This is PostgreSQL code, but this technique can by used in other database.

DEV Community: Andrey Alferov

Correct sort character data as numeric