人字或数字混合字符串的人性化或自然数排序

Question

跟随Sivaram Chintalapudi 提出的这个问题，我对在PostgreSQL中对包含多位数字和单词/字母的混合字符串进行自然（或“人性化”）排序 “ 是否可行”感兴趣，没有固定的解决方案字符串中的单词和数字的模式，并且字符串中可能有多个多位数字。

我唯一看到的常规操作是在Mac OS的Finder中，它对包含混合数字和单词的文件名进行自然排序，将“ 20”放在“ 3”之后，而不是在它之前。

所需的排序规则顺序将由一种算法产生，该算法将每个字符串在字母数字边界处拆分为多个块，然后对每个部分进行排序，将具有常规排序规则的字母块和数字块视为用于排序目的的整数。所以：

'AAA2fred'将成为('AAA',2,'fred')和'AAA10bob'将成为('AAA',10,'bob')。然后可以根据需要对它们进行排序：

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)

与通常的字符串排序规则相比：

regress=# WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') )

regress-# SELECT dat FROM dat ORDER BY dat;

    dat     

------------

 (AAA10bob)

 (AAA2fred)

(2 rows)

但是，记录比较方法不能一概而论，因为Pg不会比较ROW（..）构造或条目数不相等的记录。

给定此SQLFiddle中的样本数据，默认的en_AU.UTF -8排序规则将产生顺序：

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

但我想要：

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

目前，我正在使用PostgreSQL 9.1，但是仅9.2的建议会很好。我对如何实现有效的字符串拆分方法以及如何在上述交替的字符串-然后-数字排序规则中比较生成的拆分数据的建议感兴趣。或者，当然，在不需要分割字符串的完全不同且更好的方法上。

PostgreSQL似乎不支持比较器功能，否则可以使用递归比较器以及诸如ORDER USING comparator_fn和comparator(text,text)函数之类的方法轻松完成。syntax，这种语法是虚构的。

Answer 1

稍后添加此答案是因为看起来其他所有人似乎都在将其分解为数组或类似的数组。似乎过多。CREATE FUNCTION rr(text,int) RETURNS text AS $$SELECT regexp_replace(    regexp_replace($1, '[0-9]+', repeat('0',$2) || '\&', 'g'),     '[0-9]*([0-9]{' || $2 || '})',     '\1',     'g')$$ LANGUAGE sql;SELECT t,rr(t,9) FROM mixed ORDER BY t;      t       |             rr              --------------+----------------------------- AAA02free    | AAA000000002free AAA10bob     | AAA000000010bob AAA2bbb03boo | AAA000000002bbb000000003boo AAA2bbb3baa  | AAA000000002bbb000000003baa AAA2fred     | AAA000000002fred(5 rows)(reverse-i-search)`OD': SELECT crypt('richpass','$2$08$aJ9ko0uKa^C1krIbdValZ.dUH8D0R0dj8mqte0Xw2FjImP5B86ugC');richardh=> richardh=> SELECT t,rr(t,9) FROM mixed ORDER BY rr(t,9);      t       |             rr              --------------+----------------------------- AAA2bbb3baa  | AAA000000002bbb000000003baa AAA2bbb03boo | AAA000000002bbb000000003boo AAA2fred     | AAA000000002fred AAA02free    | AAA000000002free AAA10bob     | AAA000000010bob(5 rows)我并不是说两个正则表达式是执行此操作的最有效方法，但是rr（）是不可变的（对于固定长度），因此您可以对其进行索引。哦-这是9.1当然，使用plperl，您只需评估一下替换物即可一次性填充/修剪。但是，有了perl，您总是拥有比其他任何方法都更多的选择（TM）:-)