How Can PostgreSQL Sort Mixed Word-and-Number Strings in a Human-Friendly Order?

PostgreSQL human-friendly sorting of mixed alphanumeric strings

Humane sorting of mixed alphanumeric strings in PostgreSQL is a unique challenge. An ideal sort order would prioritize numbers over letters, putting "3" before "20" and "fred" after "10bob".

Split the string into number and string components

One way is to split each string into chunks at alphanumeric boundaries. For example, "AAA2fred" would become ("AAA", 2, "fred"). This separation allows separate sorting based on data type, using the normal collation rules for alphabetic blocks and treating numeric blocks as integers.

Using the regexp_matches() function and the '(D*)(d*)' mode with the 'g' option, we can extract these components of each string and use array_agg() to create an ai array (a composite of text and integer fields type).

Sort the split data

After splitting and converting the string into an array of composite types, we can use these arrays for sorting. The ORDER BY clause makes use of this array to ensure that the letter blocks maintain their original order within the number blocks.

Complete Solution

Combining the string splitting and sorting steps, we can achieve the desired human-like sorting using the following query:

<code class="language-sql">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;</code>

This solution works well for arbitrary data with a varying number of elements in each string, and it provides a flexible approach that can be customized to your specific sorting requirements.

The above is the detailed content of How Can PostgreSQL Sort Mixed Word-and-Number Strings in a Human-Friendly Order?. For more information, please follow other related articles on the PHP Chinese website!