如何使用正则表达式从字符串中提取指定词表的匹配内容（PostgreSQL场景）

我需要使用正则表达式从地址类字符串中提取固定词表（数组）中的指定词汇，例如街道方向缩写East、NW、North West等，词表内词汇以空格分隔，可出现在字符串任意位置（开头或末尾附近均可）。

我目前找到的最接近的方案是Python环境下从字符串中提取词表内单个量词的实现，但我使用的是SQL，且该方案的目标词遵循特定顺序/规则，不符合我的需求。

我的问题是：
是否可以无需循环，仅通过正则表达式提取字符串中所有匹配词表的内容？

我使用SQL/PostgreSQL，提供可适配PostgreSQL正则语法的标准正则表达式方案最佳。

示例词表如下：ARRAY['east', 'south', 'west', 'north']

PostgreSQL原生支持无需循环的正则全量匹配方案，核心使用regexp_matches函数实现，具体如下：

核心正则规则 #

使用PostgreSQL POSIX正则的词首锚点\m、词尾锚点\M包裹词表的多选分支，格式为：
\m(词1|词2|词3...)\M

• \m/\M仅匹配单词的开头和结尾，避免提取到其他词汇的内嵌片段（比如不会把eastern中的east误匹配）
• 多选分支需按词汇长度降序排列，避免短词优先匹配截断长词（例如同时有North West和West时，需把North West放在分支前面）

固定词表查询示例 #

针对示例词表，直接拼接正则即可，以下查询会返回每条地址对应的所有匹配方向词数组：

SELECT
  address_str,
  -- 提取所有匹配项，g代表全局匹配，i代表忽略大小写，可按需调整
  ARRAY(SELECT unnest(regexp_matches(address_str, '\m(east|south|west|north)\M', 'gi'))) AS matched_directions
FROM addresses;

动态词表查询示例 #

如果词表是存储在数组字段中的动态值，可通过SQL动态拼接正则：

WITH word_config AS (
  -- 此处可替换为实际读取词表的逻辑
  SELECT ARRAY['east', 'south', 'west', 'north', 'north west', 'nw'] AS direction_words
)
SELECT
  address_str,
  ARRAY(SELECT unnest(regexp_matches(address_str,
    '\m(' || array_to_string(
      -- 按词长度降序排序，避免短词截断长词
      (SELECT array_agg(word ORDER BY length(word) DESC) FROM unnest(direction_words) AS t(word)),
      '|'
    ) || ')\M', 'gi'
  ))) AS matched_directions
FROM addresses, word_config;

说明 #

• 不需要忽略大小写的场景可去掉正则参数中的i标识
• 词表中如果包含.+*?()等正则特殊字符，拼接前需用regexp_escape(word)转义
• 无匹配项时返回的结果为null，可通过coalesce函数替换为空数组

内容的提问来源于stack exchange，提问作者thor