0

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.

gameon67
  • 3,981
  • 5
  • 35
  • 61

1 Answers1

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
Hong Van Vit
  • 2,884
  • 3
  • 18
  • 43
  • 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? – gameon67 Apr 14 '20 at 03:13
  • Thank you! I see you add substring inside a substring – gameon67 Apr 14 '20 at 03:48