PostgreSQL中Unicode类\p{Lo}的替代方案咨询

PostgreSQL的POSIX正则表达式不支持\p{Lo}这类Unicode类别简写，以下是两种可靠的替代实现方式：

1. 生成完整的Unicode范围字符类 #

利用PostgreSQL自带的pg_catalog.pg_unicode系统表，可以直接生成覆盖所有Lo类别的正则字符范围。

操作步骤： #

1. 执行以下查询生成完整的Lo范围字符串：

SELECT string_agg(
  CASE WHEN start_code = end_code THEN chr(start_code)
       ELSE chr(start_code) || '-' || chr(end_code)
  END, ''
) AS lo_regex_range
FROM (
  SELECT 
    min(code) AS start_code,
    max(code) AS end_code
  FROM pg_catalog.pg_unicode
  WHERE category = 'Lo'
  GROUP BY (code - row_number() OVER (ORDER BY code))
) AS char_ranges;

2. 将查询结果包裹在[]中，作为正则表达式的字符类使用，比如生成结果为Ͱ-ϿЀ-ӿ...，则用[Ͱ-ϿЀ-ӿ...]替代\p{Lo}。

2. 自定义函数实现类别判断 #

如果直接使用范围字符串过长、维护不便，可以编写自定义函数判断单个字符是否属于Lo类别，再结合查询逻辑使用。

创建判断函数： #

CREATE OR REPLACE FUNCTION is_lo_char(c text)
RETURNS boolean AS $$
BEGIN
  IF length(c) != 1 THEN
    RETURN FALSE;
  END IF;
  RETURN (SELECT category = 'Lo' FROM pg_catalog.pg_unicode WHERE code = ascii(c));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用示例： #

• 匹配包含至少一个Lo字符的字符串：

SELECT * FROM your_table
WHERE EXISTS (
  SELECT 1 FROM unnest(string_to_array(your_column, NULL)) AS ch
  WHERE is_lo_char(ch)
);

• 提取字符串中的所有Lo字符：

SELECT string_agg(ch, '') AS lo_chars
FROM unnest(string_to_array(your_column, NULL)) AS ch
WHERE is_lo_char(ch);

注意事项 #

• 第一种方案生成的范围基于当前PostgreSQL版本的Unicode数据集，版本更新后建议重新生成。
• 自定义函数在处理大文本量时性能略逊于直接使用正则范围，可根据实际场景选择。

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