This discussion is archived
4 Replies Latest reply: Mar 29, 2012 2:27 AM by Merijn8106997 RSS

string manipulation: remove duplicate items from string

Merijn8106997 Newbie
Currently Being Moderated
Hi,

I am trying to deduplicate entries from a string with ; as a delimiter

example input string: UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV23_I2_P1_NUM;UV23_I2_P0_NUM;UV24_I4_P0_TXT

example output string: UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV23_I2_P1_NUM;UV24_I4_P0_TXT

notice the duplicate entry UV23_I2_P0_NUM in the first string which is removed in the output.

Thanks!
  • 1. Re: string manipulation: remove duplicate items from string
    jeneesh Guru
    Currently Being Moderated
    One way (11g)
    SQL> with t as
      2  (select 'UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV23_I2_P1_NUM;UV23_I2_P0_NUM;UV24_I4_P0_TXT' str from dual)
      3  select listagg(str,';') within group(order by str) str
      4  from(
      5  select distinct regexp_substr(str,'[^;]+',1,level) str
      6  from t
      7  connect by regexp_substr(str,'[^;]+',1,level) is not null);
    
    STR
    ----------------------------------------------------------------------------------------------------------------
    
    UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV24_I4_P0_TXT
  • 2. Re: string manipulation: remove duplicate items from string
    Merijn8106997 Newbie
    Currently Being Moderated
    Sorry, forgot to mention that my database is 10.2...

    solutions with pl/sql functions are also welcome as the string is being used in a procedure

    Edited by: Merijn8106997 on 29-mrt-2012 1:41
  • 3. Re: string manipulation: remove duplicate items from string
    908002 Expert
    Currently Being Moderated
    SELECT  distinct  
                         REGEXP_SUBSTR ('UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV23_I2_P1_NUM;UV23_I2_P0_NUM;UV24_I4_P0_TXT',
                                        '[^;]+',
                                        1,
                                        LEVEL
                                       ) AS val
                    FROM dual
              CONNECT BY REGEXP_SUBSTR ('UV23_I2_P0_NUM;UV23_I2_P1_NUM;UV23_I2_P1_NUM;UV23_I2_P0_NUM;UV24_I4_P0_TXT',
                                      '[^;]+',  1,
                                        LEVEL
                                       ) IS NOT NULL;
  • 4. Re: string manipulation: remove duplicate items from string
    Merijn8106997 Newbie
    Currently Being Moderated
    Thanks Kiran!

    It works perfectly

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points