Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Following up on this question by Sivaram Chintalapudi, I'm interested in whether it's practical in PostgreSQL to do natural - or "humanized" - sorting " of strings that contain a mixture of multi-digit numbers and words/letters. There is no fixed pattern of words and numbers in the strings, and there may be more than one multi-digit number in a string.

The only place I've seen this done routinely is in the Mac OS's Finder, which sorts filenames containing mixed numbers and words naturally, placing "20" after "3", not before it.

The collation order desired would be produced by an algorithm that split each string into blocks at letter-number boundaries, then ordered each part, treating letter-blocks with normal collation and number-blocks as integers for collation purposes. So:

'AAA2fred' would become ('AAA',2,'fred') and 'AAA10bob' would become ('AAA',10,'bob'). These can then be sorted as desired:

regress=# WITH dat AS ( VALUES ('AAA',2,'fred'), ('AAA',10,'bob') ) regress-# SELECT dat FROM dat ORDER BY dat;      dat       --------------  (AAA,2,fred)  (AAA,10,bob) (2 rows) 

as compared to the usual string collation ordering:

regress=# WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') ) regress-# SELECT dat FROM dat ORDER BY dat;     dat      ------------  (AAA10bob)  (AAA2fred) (2 rows) 

However, the record comparison approach doesn't generalize because Pg won't compare ROW(..) constructs or records of unequal numbers of entries.

Given the sample data in this SQLFiddle the default en_AU.UTF-8 collation produces the ordering:

1A, 10A, 2A, AAA10B, AAA11B, AAA1BB, AAA20B, AAA21B, X10C10, X10C2, X1C1, X1C10, X1C3, X1C30, X1C4, X2C1 

but I want:

1A, 2A, 10A, AAA1BB, AAA10B, AAA11B, AAA20B, AAA21B, X1C1, X1C3, X1C4, X1C10, X1C30, X2C1, X10C10, X10C2 

I'm working with PostgreSQL 9.1 at the moment, but 9.2-only suggestions would be fine. I'm interested in advice on how to achieve an efficient string-splitting method, and how to then compare the resulting split data in the alternating string-then-number collation described. Or, of course, on entirely different and better approaches that don't require splitting strings.

PostgreSQL doesn't seem to support comparator functions, otherwise this could be done fairly easily with a recursive comparator and something like ORDER USING comparator_fn and a comparator(text,text) function. Alas, that syntax is imaginary.

Update: Blog post on the topic.

like image 898
Craig Ringer Avatar asked Oct 18 '12 23:10

Craig Ringer


2 Answers

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

like image 119
Erwin Brandstetter Avatar answered Sep 23 '22 02:09

Erwin Brandstetter


I faced this same problem, and I wanted to wrap the solution in a function so I could re-use it easily. I created the following function to achieve a 'human style' sort order in Postgres.

CREATE OR REPLACE FUNCTION human_sort(text)   RETURNS text[] AS $BODY$      /* Split the input text into contiguous chunks where no numbers appear,      and contiguous chunks of only numbers. For the numbers, add leading       zeros to 20 digits, so we can use one text array, but sort the       numbers as if they were big integers.         For example, human_sort('Run 12 Miles') gives             {'Run ', '00000000000000000012', ' Miles'}   */   select array_agg(     case       when a.match_array[1]::text is not null          then a.match_array[1]::text                else lpad(a.match_array[2]::text, 20::int, '0'::text)::text                                           end::text)     from (       select regexp_matches(         case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'       ) AS match_array           ) AS a   $BODY$   LANGUAGE sql IMMUTABLE; 

tested to work on Postgres 8.3.18 and 9.3.5

  • No recursion, should be faster than recursive solutions
  • Can be used in just the order by clause, don't have to deal with primary key or ctid
  • Works for any select (don't even need a PK or ctid)
  • Simpler than some other solutions, should be easier to extend and maintain
  • Suitable for use in a functional index to improve performance
  • Works on Postgres v8.3 or higher
  • Allows an unlimited number of text/number alternations in the input
  • Uses just one regex, should be faster than versions with multiple regexes
  • Numbers longer than 20 digits are ordered by their first 20 digits

Here's an example usage:

select * from (values    ('Books 1', 9),   ('Book 20 Chapter 1', 8),   ('Book 3 Suffix 1', 7),   ('Book 3 Chapter 20', 6),   ('Book 3 Chapter 2', 5),   ('Book 3 Chapter 1', 4),   ('Book 1 Chapter 20', 3),   ('Book 1 Chapter 3', 2),   ('Book 1 Chapter 1', 1),   ('', 0),   (null::text, 0) ) as a(name, sort) order by human_sort(a.name) ----------------------------- |name               |  sort | ----------------------------- |                   |   0   | |                   |   0   | |Book 1 Chapter 1   |   1   | |Book 1 Chapter 3   |   2   | |Book 1 Chapter 20  |   3   | |Book 3 Chapter 1   |   4   | |Book 3 Chapter 2   |   5   | |Book 3 Chapter 20  |   6   | |Book 3 Suffix 1    |   7   | |Book 20 Chapter 1  |   8   | |Books 1            |   9   | ----------------------------- 
like image 43
TNelson Avatar answered Sep 25 '22 02:09

TNelson