DEV Community

Andrey Alferov
Andrey Alferov

Posted on

Correct sort character data as numeric

#postgres

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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)