从字符串中删除关键字列表

Question

我有一个包含一团文本的数据列。例如，"约翰·多伊住在翡翠街"。我想从字符串中删除文本列表。我有一些这样的清单(住，在，街道)。我想选择一个新的数据列，将文本转换为"John Doe Emerald"。

Answer 1

首先，您可以基于replace函数创建一个AGGREGATE函数:

CREATE OR REPLACE FUNCTION replace(x text, y text, old_txt text, new_txt text)
RETURNS text LANGUAGE sql IMMUTABLE AS
$$
SELECT replace(COALESCE(x,y), old_txt, new_txt)
$$ ;
DROP AGGREGATE IF EXISTS replace_agg (text, text, text) CASCADE ;
CREATE AGGREGATE replace_agg (text, text, text)
(
sfunc = replace
, stype = text
);

然后你可以在SELECT语句中迭代replace_agg函数:

SELECT replace_agg (your_table.your_column, k.keyword, '')
FROM (SELECT 'John Doe lives in Emerald Street' as your_column) as your_table
CROSS JOIN (SELECT unnest(array['lives', 'in', 'Street']) as keyword) as k

，结果如下:

'John Doe   Emerald'

Answer 2

假设简单的情况:

• 在表和替换字符串中，单词由单个空格字符分隔。
• 没有自然语言中的标点符号。无前后噪声。
• 区分大小写匹配。
• 删除所有匹配项(而不是只有第一个)。

和如下表:

CREATE TABLE strings(id serial PRIMARY KEY, string text);
INSERT INTO strings(string) VALUES
('John Doe lives in Emerald Street')
, ('John Doe lives in Emerald Street as john DOE');

regexp_replace()

一个简短的解决方案:

SELECT *, rtrim(regexp_replace(string, '(John|Doe|Emerald) ?', '', 'g')) FROM strings;

|分隔正则表达式中的可选分支。

相关:

• 使用PostgreSQL
修剪尾随空格

或者，使用原始替换字符串作为输入:

SELECT *, rtrim(regexp_replace(string, '(' || replace('John Doe Emerald', ' ', '|') || ') ?', '', 'g')) FROM strings;

设置操作正则表达式通常开销很大。这可能更快(最小形式):

SELECT s.id, string_agg(word, ' ') AS string2
FROM   strings s, unnest(string_to_array(s.string, ' ')) word
WHERE  word <> ALL (string_to_array('John Doe Emerald', ' '))
GROUP  BY 1
ORDER  BY 1;

避免歧义，并确保保持原顺序:

SELECT s.id, string_agg(word, ' ' ORDER BY ord) AS string2
FROM   strings s, unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))
GROUP  BY 1
ORDER  BY 1;

:

• PostgreSQL unnest() with element number

在单独的子查询中ORDER BY通常更快:

SELECT sub.id, string_agg(sub.word, ' ') AS string2
FROM  (
SELECT s.id, t.word
FROM   strings s, unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))
ORDER  BY s.id, t.ord
) sub
GROUP  BY 1
ORDER  BY 1;

通常更容易与LATERAL子查询集成:

SELECT s.id, sub.string2
FROM   strings s
CROSS  JOIN LATERAL (
SELECT string_agg(t.word, ' ' ORDER BY t.ord) AS string2
FROM   unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))   
) sub
ORDER  BY 1;

这样，我们就不需要在外层的SELECT中使用GROUP BY了。

db<此处小提琴>