Vertica Quick Tip: Remove Duplicate Values from a String

This blog post was authored by Jim Knicely.

Ever wonder how to get rid of those pesky duplicate values from a string? One way of doing that is via the 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!