在 PostgreSQL 中用正则表达式提取和替换文本-未完纪

在为 Crunchy Spatial 和 Crunchy Bridge 客户提供支持时，原作者一直在思考自己通常如何清理杂乱数据，因此想谈谈正则表达式与 Postgres。正则表达式的名声不太好：难读，不同平台的实现不一致，执行速度也可能很慢。这些都可能是真的。但如果尚未掌握正则表达式，你就缺少了一项会在整个职业生涯中反复用到的数据处理技能。

正则表达式出现在所有需要处理字符串信息的工具中，包括脚本语言、文本编辑器，当然也包括数据库。

PostgreSQL 内置完整的正则表达式引擎，可以在多种场景中发挥其全部能力。

快速复习正则表达式

如果完全不了解正则表达式，可以先学习一个入门教程，熟悉基础。下面列出一些常用组成部分，以及后面查询中会用到的表达式。

. 匹配任意字符。
\s 匹配空格、制表符等空白字符。
\S 与 \s 相反，匹配任何非空白字符。
\d 匹配任意数字字符，\D 与之相反。
\w 匹配“单词”字符，例如 a-z、0-9；\W 与之相反。
^ 将模式锚定到输入开头。
$ 将模式锚定到输入结尾。
() 将模式的一部分标记为可用于后续处理的匹配组。
* 表示前面的字符重复 0 到 N 次。
+ 表示前面的字符重复 1 到 N 次。
{N} 表示前面的字符重复 N 次。

将这些组合起来：

^A+ 匹配以一个或多个 A 开头的字符串。
\d+ 匹配一个或多个连续数字组成的任意组合。
^\S 匹配不是以空白字符开头的字符串。

使用 ~ 运算符进行真假匹配

PostgreSQL 中最简单的正则表达式用法是 ~ 运算符，以及与它类似的 ~*。

value ~ regex 用右侧的正则表达式检查左侧值，如果能够在值中找到匹配，就返回 true。注意，不需要匹配整个值，只要匹配其中一部分即可。

value ~* regex 的作用相同，但不区分大小写。

例如，某个地址字符串是否含有“Avenue”这样的内容？

SELECT '100 Byron Avenue' ~ ' Avenue$'

或者，字符串是否以字母 T 开头，无论大小写？

SELECT 'theorem' ~* '^T'

更复杂一些：字符串中的数字是否构成北美电话号码，即 3 位区号、3 位交换区代码和 4 位本地号码？

SELECT '(416) 555-1212' ~* '^\D*\d{3}\D*\d{3}\D*\d{4}\D*$'

用文字描述这个正则表达式，就是：

从字符串开头开始，^。
任意数量的非数字杂项字符，\D*。
接着是三个数字，\d{3}。
中间可以有任意数量的非数字字符，\D*。
再接着三个数字，\d{3}。
中间再次允许任意数量的非数字字符，\D*。
然后是四个数字，\d{4}。
再允许任意数量的非数字字符，\D*。
一直匹配到字符串结尾，$。

用正则表达式提取文本

从字符串提取文本时，很容易直接想到功能强大的 regexp_match()。但如果只需要提取一部分内容，使用 substring() 的一种特殊形式可能更简单。

这里提取“电话号码”输入中的最后四个数字：

SELECT substring('(416) 555-1212' from '\d{4}');

如果需要在模式中加入用于定位的文字，仍然可以使用 () 标记真正关心的部分，只提取该部分：

SELECT substring('(416) 555-1212' from '\-(\d{4})');

用正则表达式替换文本

regexp_replace(value, regex, replacement, flags) 相对简单：接收要修改的值、用于查找的模式，以及在找到匹配时使用的替换字符串。

例如，通过去掉所有非数字字符，将电话号码字符串规范化：

SELECT regexp_replace('(416) 555-1212', '\D', '');

 regexp_replace
----------------
 416) 555-1212

这并不是想要的结果！原因在于 regexp_replace() 默认只处理第一个匹配。要处理每一次匹配，需要加入代表“全局”的 g 选项：

SELECT regexp_replace('(416) 555-1212', '\D', '', 'g');

 regexp_replace
----------------
 4165551212

正则表达式标志

regexp_replace() 和 regexp_match() 都可以接收可选的最后一个 flags 参数。标志很多，最常用的包括：

g：全局匹配，允许多个匹配。
i：不区分大小写。
n：匹配模式时不跨越换行符。

提取更多文本

前面已经看到，可以在正则表达式中用 () 标记匹配部分，从输入中提取子字符串。如果要提取不止一个子字符串，就应该使用 regexp_match()。

SELECT regexp_match('(416) 555-1212', '^\D*(\d{3})\D*(\d{3})\D*(\d{4})\D*$');

  regexp_match
----------------
 {416,555,1212}

这与之前的电话号码模式相同，只是每个号码组成部分现在都用 () 包围起来。

因为 regexp_match() 可能返回多个匹配组，返回值是文本数组。可以使用普通数组下标获取其中某一部分：

WITH regex AS (
  SELECT regexp_match('(416) 555-1212',
                      '^\D*(\d{3})\D*(\d{3})\D*(\d{4})\D*$') AS match
  )
SELECT match[1] AS area_code,
       match[2] AS exchange,
       match[3] AS local
FROM regex;

 area_code | exchange | local
-----------+----------+-------
 416       | 555      | 1212