CODEWITHSUNDEEP
postgresqlsorting
iv67
iv67

Reputation: 4151

How to sort Alphanumeric using Postgresql (specific data)?

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.

Upvotes: 0

Views: 58

Answers (1)

Hong Van Vit
Hong Van Vit

Reputation: 2986

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

Upvotes: 1

Related Questions