CODEWITHSUNDEEP
stringattributessubstringpostgresql-9.2
spatialhast
spatialhast

Reputation: 271

Delete substring in string of PostgreSQL table

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!

Upvotes: 3

Views: 6824

Answers (1)

spatialhast
spatialhast

Reputation: 271

Solution:

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

or 

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

Upvotes: 5

Related Questions