pgsql 比较数字字符串_postgresql – 使用比较运算符比较postgres中的字符串？

大多数字符串比较默认为所谓的

ASCIIbetical sorting.它将每个字符转换为其ASCII编号(或者可能是UTF-8,其与前7位的ASCII重叠)然后进行排序.这看起来很好……

select 'a' < 'z'; -- true

……但很快就会出错.

select 'a' < 'Z'; -- false

这是因为所有大写字母都在ASCII和UTF-8中的所有小写字母之后.

解决这个问题的简单方法是通过在两侧调用upper或lower来规范化大小写.

select lower('a') < lower('Z'); -- true

ASCIIbetical排序的第二个问题是数字.再次,它开始很好……

select 'a9' < 'a1'; -- true

……但很快就去了锅.

select 'a9' < 'a10'; -- false

ASCIIbetical排序一次比较一个字符. ASCII 9(57)小于ASCII 1(49).

你想要的是一个natural sort,其中字符串部分作为字符串进行比较,数字作为数字进行比较.在SQL中进行自然排序并不是最简单的事情.您需要固定的字段宽度来分别对每个子字符串进行排序,或者可能需要使用正则表达式…

幸运的是,pg_natural_sort_order是Postgres扩展,实现了高效的自然排序.

如果您无法安装扩展,则可以使用存储过程,例如2kan的btrsort.

CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$

SELECT

CASE WHEN $1 ~ '^[^0-9]+' THEN

COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ), '' )

ELSE

COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ), '' )

END

$$LANGUAGE SQL;

CREATE FUNCTION btrsort(text) RETURNS text AS $$

SELECT

CASE WHEN char_length($1)>0 THEN

CASE WHEN $1 ~ '^[^0-9]+' THEN

RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))

ELSE

LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))

END

ELSE

$1

END

;

$$LANGUAGE SQL;

虽然它没有提供比较运算符,但我不会假装理解它.这允许您按顺序使用它.

select * from things order by btrsort(whatever);

create index things_whatever_btrsort_idx ON things( btrsort(whatever) );