PostgreSQL用正则移除特殊字符时如何保留单词间单个空格

sig字段空格被全部清除，是两层正则的逻辑共同导致的：

1. 第一层替换非法字符时，你写的允许字符集[^0-9a-zA-Z:/]没包含空格，原文本里的正常空格会被判定为非法字符，替换成新的空格——如果原文本里有特殊字符和空格相邻的情况，这一步会生成大量连续空格。
2. 第二层去重空格的正则逻辑有缺陷：你写的 +(?= )正向预查规则，在PostgreSQL的POSIX贪婪匹配+全局替换模式下，预查命中的空格不会被匹配消耗，会被下一轮替换重复命中，逐轮把所有连续空格都替换为空，最后连单词间的单个空格都剩不下。

顺带一提，medname字段用了同款去空格正则没出问题，只是因为medname第一层替换是把非法字符直接删成空串，不会额外生成连续空格，属于侥幸没触发bug，不是正则写对了。

直接把嵌套绕弯的正则替换改成逻辑更直白的写法，从源头避免匹配bug，修正后完整代码如下：

WITH dbl_medications AS (
    SELECT * 
    FROM dblink('select medname, sig, form from medications')
    AS t1(medname text, sig text, form text)
    ORDER BY medname, form, sig
)
INSERT INTO medications (medname, sig, form)    
SELECT 
    -- medname字段处理：保留字母、数字、空格、/、-
    BTRIM(REGEXP_REPLACE(LOWER(REGEXP_REPLACE(medname, '[^a-zA-Z0-9 /-]', '', 'g')), '\s+', ' ', 'g')),
    -- sig字段处理：保留字母、数字、空格、:、/，单词间仅留单个空格
    BTRIM(REGEXP_REPLACE(LOWER(REGEXP_REPLACE(sig, '[^0-9a-zA-Z:/\s]', ' ', 'g')), '\s+', ' ', 'g')),
    -- form字段处理：仅保留字母
    LOWER(REGEXP_REPLACE(form, '[^a-zA-Z]', '', 'g'))         
FROM dbl_medications
ORDER BY 1,3,2
ON CONFLICT (medname, sig, form) DO NOTHING;

• 给sig的第一层合法字符集加了\s匹配空白字符，原文本里的正常空格不会被重复替换，从源头减少无意义的连续空格生成
• 把原来容易出匹配bug的正向预查去空格逻辑，改成直接用\s+匹配所有连续空白（含多个空格、制表符等未覆盖的空白类型），统一替换成单个空格，逻辑稳定可依赖，不会出现空格全被删光的问题
• 用内置函数BTRIM()替代原来的正则匹配去首尾空格，执行效率更高，写法更简洁
• 修正后效果完全匹配需求：所有特殊字符被清除，单词之间仅保留1个空格，适配跨数据库迁移的文本清洗要求。

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