string - 删除 PostgreSQL 表字符串中的子字符串

Question

我在 PostgreSQL 数据库中有表“A”:

name     fullname
A         A,I,A
B         B,B,S,B
D         D,D,S,E,D
E         E,E,D,G,E
F         F,F,G,F
G         G,E,G,F,G,H,N,G

如何接收带有“正确名称”列(全名)的表“B”，其中每一行都没有来自“名称”字段的匹配子字符串:

name  correctname
A      I
B      S
D      S,E
E      D,G
F      G
G      E,F,H,N

数据样本:

CREATE TABLE test(name TEXT, fullname TEXT);

INSERT INTO test(name, fullname) VALUES('A','A,I,A');
INSERT INTO test(name, fullname) VALUES('B','B,B,S,B');
INSERT INTO test(name, fullname) VALUES('D','D,D,S,E,D');
INSERT INTO test(name, fullname) VALUES('E','E,E,D,G,E');
INSERT INTO test(name, fullname) VALUES('F','F,F,G,F');
INSERT INTO test(name, fullname) VALUES('G','G,E,G,F,G,H,N,G');

谢谢!

Answer 1

Solution :

SELECT 
name
,trim(
REGEXP_REPLACE(fullname,'(,|^)('||name||'(,|$))+',',','cg')
,',')
FROM data;

或

SELECT REPLACE(REPLACE(fullname, name||',', ''), ','||name, '') FROM test;