Remove Duplicate Values from a String

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser

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!

Sign In or Register to comment.