Humanized or natural number sorting of mixed word-and-number strings

by

Building on your test data, but this works with arbitrary data. This works with any number of elements in the string.

Register a composite type made up of one text and one integer value once per database. I call it ai:

CREATE TYPE ai AS (a text, i int);

The trick is to form an array of ai from each value in the column.

regexp_matches() with the pattern (\D*)(\d*) and the g option returns one row for every combination of letters and numbers. Plus one irrelevant dangling row with two empty strings '{"",""}' Filtering or suppressing it would just add cost. Aggregate this into an array, after replacing empty strings ('') with 0 in the integer component (as '' cannot be cast to integer).

NULL values sort first – or you have to special case them – or use the whole shebang in a STRICT function like @Craig proposes.

Postgres 9.4 or later

SELECT data
FROM   alnum
ORDER  BY ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai
                FROM regexp_matches(data, '(\D*)(\d*)', 'g') x)
        , data;

db<>fiddle here

Postgres 9.1 (original answer)

Tested with PostgreSQL 9.1.5, where regexp_replace() had a slightly different behavior.

SELECT data
FROM  (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM   alnum
    ) x
GROUP  BY ctid, data   -- ctid as stand-in for a missing pk
ORDER  BY regexp_replace (left(data, 1), '[0-9]', '0')
        , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai)
        , data         -- for special case of trailing 0

Add regexp_replace (left(data, 1), '[1-9]', '0') as first ORDER BY item to take care of leading digits and empty strings.

If special characters like {}()"', can occur, you’d have to escape those accordingly.
@Craig’s suggestion to use a ROW expression takes care of that.


BTW, this won’t execute in sqlfiddle, but it does in my db cluster. JDBC is not up to it. sqlfiddle complains:

Method org.postgresql.jdbc3.Jdbc3Array.getArrayImpl(long,int,Map) is
not yet implemented.

This has since been fixed: http://sqlfiddle.com/#!17/fad6e/1

More Related Contents:

  1. Alphanumeric sorting with PostgreSQL
  2. Natural Sort in MySQL
  3. Sorting null values after all others, except special
  4. PostgreSQL ORDER BY issue – natural sort
  5. How to return result of a SELECT inside a function in PostgreSQL?
  6. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  7. Possible to perform cross-database queries with PostgreSQL?
  8. Reset auto increment counter in postgres
  9. Pass multiple values in single parameter
  10. Custom SERIAL / autoincrement per group of values
  11. How do I determine the last day of the previous month using PostgreSQL?
  12. postgres column “X” does not exist
  13. Multiple CTE in single query
  14. Return pre-UPDATE column values using SQL only
  15. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  16. Dynamic Sorting within SQL Stored Procedures
  17. PostgreSQL: encoding problems on Windows when using psql command line utility
  18. How much disk-space is needed to store a NULL value using postgresql DB?
  19. Aggregate a single column in query with many columns
  20. Aggregating (x,y) coordinate point clouds in PostgreSQL
  21. PostgreSQL convert columns to rows? Transpose?
  22. Does Postgres support nested or autonomous transactions?
  23. PostgreSQL delete with inner join
  24. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  25. Update a column of a table with a column of another table in PostgreSQL
  26. Check if NULL exists in Postgres array
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment