DEV Community

Andrey Alferov
Andrey Alferov

Posted on

3

Correct sort character data as numeric

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.

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

πŸ‘‹ Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay