1 2 Previous Next 25 Replies Latest reply: Feb 15, 2013 2:01 AM by chris227 RSS

    Remove Duplicates From Comma Separated String

    990568
      Hi,
      I have one Column which contains duplicates values with comma separated.

      Customer ID
      -----------------------------
      5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2

      I wrote this:

      select REGEXP_REPLACE('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', '(^|,)([^,]*)(,\2)+','\1\2') from dual;
      ---------------------------------
      5,6,5,6,7,4,1,2,1,4,7,2

      But it eliminates only continuous elements.
      I want out put like:
      ---------------------
      5,6,7,4,1,2

      Please help.
      Thanks.
      Amit

      Edited by: 987565 on Feb 12, 2013 4:02 AM
        • 1. Re: Remove Duplicates From Comma Separated String
          BluShadow
          987565 wrote:
          Hi,
          I have one Column which contains duplicates values with comma separated.
          Well there's your first problem. Why are you storing data like that. It's completely against 3rd normal form of relational database design.
          Customer ID
          -----------------------------
          5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,,4,7,2

          I wrote this:

          select REGEXP_REPLACE('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,,4,7,2', '(^|,)([^,]*)(,\2)+','\1\2') from dual;
          ---------------------------------
          5,6,5,6,7,4,1,2,1,4,7,2

          But it eliminates only continuous elements.
          I want out put like:
          ---------------------
          5,6,7,4,1,2
          You'll have to split the string into individual elements, then distinct them, and then (if you really must), aggregate the elements back together to a single string again.
          • 2. Re: Remove Duplicates From Comma Separated String
            jeneesh
            {message:id=10725915}
            • 3. Re: Remove Duplicates From Comma Separated String
              Frank Kulash
              Hi,

              Welcome to the forum!

              I think the simpletst way would be to
              (1) Split the sting into multiple rows, with one number per row. See {message:id=3702412}
              (2) Use GROUP BY or SELECT DISTINCT to ge the unique numbers
              (3) Use any string aggregation technique to re-combine the numbers into a comma-delimited string.
              • 4. Re: Remove Duplicates From Comma Separated String
                Purvesh K
                Works 11.2g Onwards
                with data as
                (
                  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
                )
                select listagg(col, ',') within group(order by 1) col
                  from (
                        select distinct regexp_substr(col, '[^,]+', 1, level) col
                          from data
                        connect by level <= regexp_count(col, ',')
                       )
                
                COL
                -------------
                1,2,4,5,6,7
                • 5. Re: Remove Duplicates From Comma Separated String
                  971895
                  One more..........
                  select distinct token  from (SELECT TRIM( SUBSTR ( txt
                                       , INSTR (txt, ',', 1, level ) + 1
                                       , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1
                                       )
                              )
                             AS token           
                      FROM ( SELECT ','||'5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2'||',' AS txt  FROM dual )
                      CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1) order by 1
                      
                  • 6. Re: Remove Duplicates From Comma Separated String
                    990568
                    Thanks,
                    This is giving values in columns like
                    1
                    2
                    4
                    5
                    6
                    7

                    ----
                    Result require is
                    ---------------------
                    1,2,4,5,6,7

                    And i cant use LISTAGG, we are using 11.1

                    Edited by: 987565 on Feb 12, 2013 4:28 AM

                    Edited by: 987565 on Feb 12, 2013 4:29 AM
                    • 7. Re: Remove Duplicates From Comma Separated String
                      BluShadow
                      987565 wrote:
                      Thanks,
                      This is giving values in columns like
                      1
                      2
                      4
                      5
                      6
                      7

                      ----
                      Result require is
                      ---------------------
                      1,2,4,5,6,7

                      And i cant use LISTAGG, we are using 11.1
                      So use one of the string aggregation techniques in the link you've already been given, or as linked to in the FAQ: {message:id=9360005}
                      • 8. Re: Remove Duplicates From Comma Separated String
                        Purvesh K
                        This version works on 11.1.
                        with data as
                        (
                          select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
                        )
                        select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by  rn - 1), ',') col
                          from (
                                select col, row_number() over (order by 1) rn
                                  from (
                                        select distinct regexp_substr(col, '[^,]+', 1, level) col
                                          from data
                                        connect by level <= length(col) - length(replace(col, ','))
                                       )
                               )
                         start with rn = 1
                        connect by prior rn = rn - 1;
                        
                        COL                                                                         
                        -------------- 
                        1,2,4,5,6,7
                        • 9. Re: Remove Duplicates From Comma Separated String
                          user10756087
                          Hi,
                          select listagg(col, ',') within group(order by 1) col
                          from (
                          select distinct substr(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ','),level,1) col from dual
                          connect by level<=length(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ',')) );

                          Thanks....

                          Edited by: user10756087 on Feb 13, 2013 2:42 AM
                          • 10. Re: Remove Duplicates From Comma Separated String
                            Purvesh K
                            user10756087 wrote:
                            Hi,
                            select listagg(col, ',') within group(order by 1) col
                            from (
                            select distinct substr(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ','),level,1) col from dual
                            connect by level<=length(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ',')) );

                            Thanks....

                            Edited by: user10756087 on Feb 13, 2013 2:42 AM
                            In the above SQL, the Connect By clause is not correct. This would work correct only if you have numbers between 0 - 9, but for those exceeding 9 (two or more digit numbers), it will consider them two be Two numbers than to be a Single Integer and will execute an additional Iteration, which will be incorrect.

                            You need to subtract the String formed after replacing the Delimiter (in this case Comma) from the Original Length of string to ascertain the Number of times the Delimiter occurs in the given string. (See my earlier reply that works even prior to 11g versions)

                            Also, why are you posting your reply to me. It should have been directed to the OP, isn't it?
                            • 11. Re: Remove Duplicates From Comma Separated String
                              chris227
                              11g upwards
                              with data as
                              (
                                select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
                              )
                              , r (s, c) as (
                              select
                                  cast(regexp_substr(col,'\d+')  as varchar2(4000)) --alter the 4000 to your needs, the lower than lesser memory consumption
                                 ,replace(col
                                         ,regexp_substr(col,'\d+')
                                  )
                              from data
                              union all
                              select
                                  s||','||regexp_substr(c,'\d+')
                                 ,replace(c
                                         ,regexp_substr(c,'\d+')
                                  )
                              from r
                              )
                              cycle c set is_cycle to 1 default 0
                              
                              select
                              s
                              from r    
                              where
                              trim(both ',' from c) is null
                              and
                              is_cycle = 0
                              
                              S 
                              5,6,7,4,1,2 
                              Edited by: chris227 on 13.02.2013 04:19
                              simplyfied
                              • 12. Re: Remove Duplicates From Comma Separated String
                                Solomon Yakobson
                                If you don't care obout the order:
                                with t as (
                                           select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' str from dual
                                          )
                                select  x.newstr
                                  from  t,
                                        xmltable(
                                                 'string-join(distinct-values(ora:tokenize($str,",")),",")'
                                                 passing ',' || t.str as "str"
                                                 columns newstr varchar2(2000) path '.'
                                                ) x
                                /
                                
                                NEWSTR
                                ------------
                                1,2,4,5,6,7
                                SY.
                                • 13. Re: Remove Duplicates From Comma Separated String
                                  990568
                                  Thanks Purvesh,

                                  While testing with real data, it is ignoring some values. I didn't understand why it happening.

                                  with data as
                                  (
                                  select '5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716' col from dual
                                  )
                                  select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by rn - 1), ',') col
                                  from (
                                  select col, row_number() over (order by 1) rn
                                  from (
                                  select distinct regexp_substr(col, '[^,]+', 1, level) col
                                  from data
                                  connect by level <= length(col) - length(replace(col, ','))
                                  )
                                  )
                                  start with rn = 1
                                  connect by prior rn = rn - 1;

                                  -----
                                  Result I got is:
                                  5714,5714,5716

                                  My Real Query is like that:

                                  declare
                                  cursor c1 is
                                  select o.id id
                                  from order o;
                                  v_char varchar2(200) := '';

                                  begin

                                  for a1 in c1 loop
                                  with data as
                                  (
                                  select o.cust_id as col into v_char
                                  from order o
                                  where o.id = a1.id
                                  )
                                  select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by rn - 1), ',') col
                                  from (
                                  select col, row_number() over (order by 1) rn
                                  from (
                                  select distinct regexp_substr(col, '[^,]+', 1, level) col
                                  from data
                                  connect by level <= length(col) - length(replace(col, ','))
                                  )
                                  )
                                  start with rn = 1
                                  connect by prior rn = rn - 1;

                                  SYS.dbms_output.put_line(v_char);

                                  end loop;
                                  end;

                                  Later, i will update same cust_id with v_char.
                                  • 14. Re: Remove Duplicates From Comma Separated String
                                    Solomon Yakobson
                                    987565 wrote:
                                    While testing with real data, it is ignoring some values. I didn't understand why it happening.
                                    That code is only good for one row table. It will not work for multi-row table. Use code I posted. And you do not need PL/SQL at all.

                                    SY.
                                    1 2 Previous Next