Remove Duplicate Values from a String
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
[Deleted User]
Administrator
Jim Knicely authored this tip.
Do you ever wonder how to get rid of those pesky duplicate values from a string? One way of doing that is via Vertica's REGEXP_REPLACE function.
Example:
dbadmin=> SELECT c1, REGEXP_REPLACE(c1, '(?<=\, |^)(.+?)\, (?=(.+\, )?\1(\, |$))', '') FROM test;
c1 | regexp_replace
------------------------+----------------
A, B, B, A | B, A
A, B, C, A, T | B, C, A, T
A, B | A, B
D, A, B, C, C, D, D | A, B, C, D
Z, X, Z, X, A, A, A, Q | Z, X, A, Q
*, *, *, *, * | *
(6 rows)
Have Fun!
0
Comments
Here is an update REGEX for removing duplicate values from a string:
dbadmin=> select c1, regexp_replace(regexp_replace(c1, '\s*([^|]*?)\s*'), '(?<=\,|^)(.+?)\,(?=(.+\,)?\1(\,|$))') from test; c1 | regexp_replace --------------------------------------------------------------------------------------------------------------+------------------------------------------------------------- A, B | A,B D, A, B, C, C, D, D | A,B,C,D Z, X, Z, X, A, A, A, Q | Z,X,A,Q A, B, B, A | B,A 65281,65283,65288,65296,65312,65320,65328,65352,65360,65368, 65281,65283,65288,65296,65320,65352,65360,65368 | 65312,65328,65281,65283,65288,65296,65320,65352,65360,65368 A, B, C, A, T | B,C,A,T 65283, 65283, 65320,65320 | 65283,65320 (7 rows)It handles spaces following the commas!