How Can I Sort Alphanumeric Strings Humanely in PostgreSQL?

Humanized string sorting in PostgreSQL

Humanely sorting strings containing a mix of letters and numbers can be challenging in PostgreSQL, but can be achieved using a combination of regexp_matches() and array aggregation.

Split string

The first step is to split each string into its constituent alphabetic and numeric parts. This can be done using the regexp_matches() function and regular expressions like (D*)(d*). The resulting array of matches can then be iterated over to create an array of pairs, each pair containing a text value and an integer value.

Sort these pairs

The next step is to sort these value pairs according to the desired human-friendly order. Text values ​​can be sorted in normal string sort order, while integer values ​​can be sorted as integers.

Recombine strings

Finally, the sorted pairs can be used to reassemble the original string in the desired personalized order.

PostgreSQL 9.4 or higher

In PostgreSQL 9.4 or later, the following query can be used:

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

PostgreSQL 9.1

In PostgreSQL 9.1, you can use the following query:

<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         -- 用于处理尾随 0 的特殊情况</code>

The above is the detailed content of How Can I Sort Alphanumeric Strings Humanely in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!