Delete substring in string of PostgreSQL table

Question

I have table 'A' in PostgreSQL database:

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

How receive table 'B' with column 'correctname' (fullname) where in each row no matching substrings from 'name' field:

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

Sample of data:

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');

Thanks!

I think it is more a job for the application itself than the sql server. — Thomas Ruiz
– Thomas Ruiz, Commented Aug 23, 2013 at 12:05

spatialhast · Accepted Answer · 2013-08-23 12:22:59Z

5

Solution:

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

or

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

answered Aug 23, 2013 at 12:22

spatialhast

2711 gold badge5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete substring in string of PostgreSQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related