Regex to remove duplicates in SQL

Question

I have a String coming in like this -

ABC;ABC;XYZ;PQR;ABC;PQR

And requirement is to convert this to something like this -

ABC;XYZ;PQR

Meaning a Regex should keep all Unique words and remove duplicates. Can anyone please help me with this. Is this possible in SQL?

(1) Tag your question with the database you are using. (2) Fix your data structure. Storing values in string lists is not the SQLish way to store things. — Gordon Linoff
– Gordon Linoff, Commented Jul 3, 2017 at 11:31
(1) what Database Access Language you are using (2) which database -> MySQL or — Cholowao
– Cholowao, Commented Jul 3, 2017 at 11:33
I'm on HP Vertica. I wrote a piece but its not working as expected- SELECT REGEXP_REPLACE('ABC;PQR;ABC','^(.+?)\;\1*$','\1') — Rahul Sevani
– Rahul Sevani, Commented Jul 3, 2017 at 11:35

logi-kal · Accepted Answer · 2017-07-03 12:12:06Z

2

Since HP Vertica uses PCRE regex, the following regex (with null replacement) should work:

(?<=\;|^)(.+?)\;(?=(.+\;)?\1(\;|$))

See demo.

Followe a brief explanation:

(?<=\;|^) is a positive lookbehind meaning that (.+?)\; to remove should be preceded by a semicolon or the begin of string
(.+?) is the first capturing group
(?=) is a positive lookahead containing:
- (.+\;)? any other character (ending with a semicolon)
- \1 backreference for the first capturing group
- (\;|$) must end with semicolon or EOL

answered Jul 3, 2017 at 12:12

logi-kal

7,8996 gold badges35 silver badges48 bronze badges

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

Comments

lordnik22 · Accepted Answer · 2017-07-03 11:50:10Z

-1

With T-Sql you could do:

    DECLARE @Strings NVARCHAR(MAX);
    SET @Strings = '';
    SELECT @Strings = CASE WHEN @Strings != '' THEN @Strings + ';' + [String] ELSE [String] END
    FROM (
        SELECT DISTINCT VALUE AS [String] FROM STRING_SPLIT( 'ABC;ABC;XYZ;PQR;ABC;PQR',';')
    ) AS [Strings]; 
    SELECT @Strings

answered Jul 3, 2017 at 11:50

lordnik22

501 silver badge9 bronze badges

1 Comment

Rahul Sevani Over a year ago

Thanks for responding but T SQL is not in option. I want to achieve it from a Regex if possible.

Collectives™ on Stack Overflow

Regex to remove duplicates in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related