How to sort Alphanumeric using Postgresql (specific data)?

Question

I have trouble in sorting alphanumeric data using postgresql. My data and query are shown as below

WITH x(t) AS (
    VALUES
     ('GPS-10')
    ,('GPS-1')
    ,('GPS-2')
    ,('GPS-8B')
    ,('GPS-8A')
    ,('GPS-14')
    ,('SPS-2')
    ,('SPS-14')
    )
SELECT t
FROM   x
ORDER  BY substring(t, '[^0-9-].*$'), (substring(t, '^[0-9]+'))::int

It gave me the this wrong result (GPS-2 comes after GPS-10 and GPS-14 so does the SPS data)

"GPS-1"
"GPS-10"
"GPS-14"
"GPS-2"
"GPS-8A"
"GPS-8B"
"SPS-14"
"SPS-2"

My expected result is

"GPS-1"
"GPS-2"
"GPS-8A"
"GPS-8B"
"GPS-10"
"GPS-14"
"SPS-2"
"SPS-14"

I have tried all solutions described in Alphanumeric sorting with PostgreSQL and Alphanumeric sorting with PostgreSQL and didn't work.

Hong Van Vit · Accepted Answer · 2020-04-14 03:29:49Z

1

I think you can do that, some thing like this:

   WITH x(t) AS (
    VALUES
     ('GPS-10')
    ,('GPS-1')
    ,('GPS-2')
    ,('GPS-8B')
    ,('GPS-8A')
    ,('GPS-14')
    ,('SPS-2')
    ,('SPS-14')
    )
SELECT t
FROM   x
ORDER  BY substring(t, 1,3), (substring(substring(t, 5, length(t)), '^[0-9]+'))::int

edited Apr 14, 2020 at 3:29

answered Apr 14, 2020 at 2:58

Hong Van Vit

3,0063 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iv67 Over a year ago

Sorry I forgot to add a few constraints. I edited my post and added new data. The above code will fail to process the GPS-8A and GPS-8B. Can you help me?

iv67 Over a year ago

Thank you! I see you add substring inside a substring

Collectives™ on Stack Overflow

How to sort Alphanumeric using Postgresql (specific data)?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related