postgresql - 设置 Order By 以忽略每列的标点符号

Question

是否可以通过包含 [](),; 等字符的标题字段对 PostgreSQL 查询的结果进行排序，但忽略这些标点符号并仅按文本字符排序？

我已经阅读了有关更改数据库排序规则或区域设置的文章，但没有找到任何关于如何在现有数据库上按列执行此操作的明确说明。这可能吗？

Answer 1

排序的“归一化”

您可以在 ORDER BY 中使用带有模式 '[^a-zA-Z]' 的 regexp_replace() 子句，但它只识别纯 ASCII 字母。最好使用 class shorthand '\W'它可以识别您的语言环境中的其他非 ASCII 字母，例如 äüóèß 等。或者您可以在 unaccent() 函数的帮助下即兴创作并“将所有带有变音符号元素的字符规范化为它们的基本形式。考虑这个小演示:

SELECT *
      , regexp_replace(x, '[^a-zA-Z]', '', 'g')
      , regexp_replace(x, '\W', '', 'g')
      , regexp_replace(unaccent(x), '\W', '', 'g')
FROM  (
SELECT 'XY ÖÜÄöüäĆČćč€ĞğīїıŁłŃńŇňŐőōŘřŠšŞşůŽžż‘´’„“”­–—[](),;.:̈� XY'::text AS x) t

->SQLfiddle for Postgres 9.2.
->SQLfiddle for Postgres 9.1.

正则表达式代码已在 9.2 版本中更新。我假设这就是 9.2 改进处理的原因，其中示例中的所有字母字符都匹配，而 9.1 只匹配一些。

unaccent() 由附加模块 unaccent 提供.运行:

CREATE EXTENSION unaccent;

每个数据库使用一次(Postgres 9.1+，旧版本使用 different technique)。

语言环境/整理

您必须知道 Postgres 依赖于底层操作系统的语言环境(包括整理)。排序顺序由您选择的区域设置或更具体的 LC_COLLATE 控制.此相关答案中的更多信息:
String sort order (LC_COLLATE and LC_CTYPE)

有plans to incorporate collation support into Postgres directly ，但目前不可用。

许多语言环境会忽略您描述的用于开箱即用地对字符数据进行排序的特殊字符。如果您的系统中安装了提供您正在寻找的排序顺序的语言环境，您可以在 Postgres 9.1 或更高版本中临时使用它:

SELECT foo FROM bar ORDER BY foo COLLATE "xy_XY"

要查看在您当前的 Postgres 安装中安装和可用的排序规则:

SELECT * FROM pg_collation;

不幸的是，除非您破解源代码，否则无法定义您自己的自定义排序规则。

整理规则通常受某个国家/地区使用的语言规则约束。如果仍有电话簿，则电话簿的排序顺序将在其中......您的操作系统提供它们。

例如，在 Debian Linux 中，您可以使用:

locale -a

显示所有生成的语言环境。并且:

dpkg-reconfigure locales

作为 root 用户(几种方式中的一种)生成/安装更多。