使用regexp_replace函数仅替换Postgresql中带括号文本中的空格

ddrv8njm  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(1)|浏览(256)

我只需要替换括号内文本中的所有空格
"Describe what you (tried and) what (you expected) to happen"加上逗号,如下所示:
"Describe what you (tried,and) what (you,expected) to happen"
请建议如何使用函数regexp_replace正确执行此操作。提前感谢。

mlnl4t2r

mlnl4t2r1#

用一个简单的regexp_replace函数就不那么容易了...
如果括号中最多有两个单词,则可以:

SELECT string_agg (r.res, ' ')
  FROM ( SELECT CASE
                  WHEN elt ~ '^\(' THEN elt || ',' || lead(elt) OVER ()
                  WHEN elt ~ '\)$' THEN ''
                  ELSE elt
                END AS res
           FROM regexp_split_to_table('Describe what you (tried and) what (you expected) to happen', ' ') as elt
       ) AS r
 WHERE r.res <> ''

如果括号内有两个或更多单词,则必须创建自己的aggregate函数:

CREATE OR REPLACE FUNCTION replace(x text, y text, old text, new text) RETURNS text LANGUAGE sql AS $$
  SELECT replace(COALESCE(x,y), old, new) ; $$ ;

CREATE OR REPLACE AGGREGATE replace_agg (text, text, text)
(  stype = text
,  sfunc = replace
)

而查询是:

SELECT replace_agg('Describe what you (tried and) what (you expected) to happen', elt[1], replace(elt[1], ' ', ','))
  FROM regexp_matches('Describe what you (tried and) what (you expected) to happen', '\([^\)]*\)', 'g') AS elt

参见dbfiddle中的测试

相关问题