在将文本字符串从一个数据库迁移到另一个数据库时,我需要消除所有特殊字符,并在单词之间只保留一个空格.
不幸的是,下面的代码消除了单词之间的所有空格,这不是我想要的.
Here is the code I have. The "sig" field is wrong (it removes all spaces without leaving one space). Where did I go wrong?
TIA
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 REGEXP_REPLACE(LOWER(REGEXP_REPLACE(medname,'[^a-zA-Z0-9 /-]','','g')), '^ +| +$| +(?= )', '', 'g'),
REGEXP_REPLACE(LOWER(REGEXP_REPLACE(sig,'[^0-9a-zA-Z:/]',' ','g')), '^ +| +$| +(?= )', '', 'g'),
LOWER(REGEXP_REPLACE(form,'[^a-zA-Z]','','g'))
FROM dbl_medications
ORDER BY 1,3,2
ON CONFLICT (medname, sig, form) DO NOTHING;