0

Been using the following regexp_replace to create a comma separated string for those values in 'col_name' and remove any duplicates.

REGEXP_REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,col_name,',').EXTRACT('//text()')).GetClobVal(),','),'([^,]+)(,\1)+', '\1')

Works ok but with 1 exception - if 'col_name' has empty string values. What I am ending up with is:

value1,value2,,value3,value4,,value5 etc.

Looking for that elusive regex that can remove any duplicate values and those repeating commas.

Thanks

--- updated ----

col-name ----------------- row1 value 1 row2 value 2 row3 row4 value 2 row5 value 3 row6 value 1 row7

Expected Result: value1,value2,value3

  • Provide some sample data and expected output. – JSapkota Dec 14 '17 at 4:12
0

Would the following be acceptable (not a REGEXP_REPLACE, but maybe easier to understand)?

Test table/data:

create table t as
select 'row1' c1 , 'value1' c2 from dual union all
select 'row2' ,  'value2' from dual union all
select 'row3' ,  null from dual union all
select 'row4' ,  'value2' from dual union all
select 'row5' ,  'value3' from dual union all
select 'row6' ,  'value1' from dual union all
select 'row7' ,  null from dual;

select * from t;

C1      C2
row1    value1
row2    value2
row3    null
row4    value2
row5    value3
row6    value1
row7    null

Query:

select 
  listagg(c2 , ',') within group ( order by c2 ) 
from (
  select distinct c2 
  from t
  where c2 is not null
);

result
value1,value2,value3

Dbfiddle here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.