Question 89355 in PostgreSQL Question posted in
The official documentation can be found here.

Sort table by Alphanumeric column in Postgres – Postgresql

So far, I’ve come up with

ORDER BY SUBSTRING(COLUMN FROM '[A-Z]+'), COALESCE(SUBSTRING(COLUMN FROM '(?<=-)[0-9]+')::INTEGER, 0), SUBSTRING(COLUMN FROM '(?<=()[0-9]+')::BIGINT
Outcome Desired Outcome
AC-1 AC-1
AC-2 (1) AC-2
AC-2 (2) AC-2 (1)
AC-2 AC-2 (2)
AC-3 (1) AC-3 (1)
AC-3 (2) AC-3 (2)
AC-3 (3) AC-3 (3)
Tags:

3

Answers


  1. 0

    That is simple: just define the appropriate collation.

    CREATE COLLATION mycoll (
   PROVIDER = icu,
   LOCALE = 'und-u-kn-true'
);

SELECT col FROM tab ORDER BY col COLLATE mycoll;

    With old versions of the ICU library, you might have to define the LOCALE as @colNumeric=true.

    Login or Signup to reply.
  2. 0

    Using your current data; I created fiddle.

    The following query shall work for you :

    SELECT my_column
FROM mytable
ORDER BY 
  SUBSTRING(my_column FROM '[A-Z]+') ASC, 
  COALESCE(SUBSTRING(my_column FROM '(?<=-)[0-9]+')::INTEGER, 0) ASC,
  CASE WHEN my_column ~ '^([A-Z]+-[0-9]+)$' THEN 1 ELSE 0 END DESC,
  SUBSTRING(my_column FROM '([0-9]+)*$') ASC;

    It gives me desired output as :

    my_column
AC-1
AC-2
AC-2 (1)
AC-2 (2)
AC-3 (1)
AC-3 (2)
AC-3 (3)

    Please replace my_column with your actual column_name and mytable with actual table_name

    Login or Signup to reply.
  3. 0

    As with most SQL engines, the default behavior of postgresql when comparing NULLs to other values is to put NULLs last, so when the optional number preceded by a left parenthesis fails to match and becomes NULL, it gets placed last.

    You can change the behavior to put NULLs first with the NULLS FIRST option:

    ORDER BY
  SUBSTRING(COLUMN FROM '[A-Z]+'),
  COALESCE(SUBSTRING(COLUMN FROM '(?<=-)[0-9]+')::INTEGER, 0),
  SUBSTRING(COLUMN FROM '(?<=()[0-9]+')::BIGINT NULLS FIRST
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search