How Can I Perform Natural Sorting of Mixed Alphabetic and Numeric Strings in PostgreSQL?

Natural sorting of PostgreSQL mixed alphanumeric strings

An inherent challenge in naturally sorting mixed alphanumeric strings is maintaining the order of the words while still treating the numbers as integers. In PostgreSQL 9.1 and above, a practical method exists:

Composite types and array operations:

1. Create a composite type called "ai" that contains a text field "a" and an integer field "i".
2. Split each string into an array of "ai" values ​​using the "regexp_matches" function.

Sample query (PostgreSQL 9.4 and later):

<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>

How to deal with PostgreSQL 9.1:

In older versions of PostgreSQL, the query needs to be modified slightly:

<code class="language-sql">SELECT data
FROM (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM alnum
    ) x
GROUP BY ctid, data   -- ctid作为缺少主键的占位符
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;</code>

With this approach, string splitting and natural sorting become feasible in PostgreSQL, allowing intuitive sorting of various data sets containing mixed alphanumeric strings.

The above is the detailed content of How Can I Perform Natural Sorting of Mixed Alphabetic and Numeric Strings in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!