OraFAQ Forum: SQL & PL/SQL » Distinct Comma Separated Values

Home » SQL & PL/SQL » SQL & PL/SQL » Distinct Comma Separated Values (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Distinct Comma Separated Values [message #653427]

Tue, 05 July 2016 04:43

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hi All,

I want to find distinct comma separated values from below string using SQL:

Input: TESTER,DBA,DBA,DBA,DEVELOPER,DEVELOPER,TESTER,DBA,DBA,DEVELOPER

Output: TESTER,DBA,DEVELOPER

Can anyone please help me out ?

Thanks,
Xandot

Report message to a moderator

Re: Distinct Comma Separated Values [message #653429 is a reply to message #653427]

Tue, 05 July 2016 05:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

3 steps task: split the string into components, use distinct, aggregate the result using listagg.

Report message to a moderator

Re: Distinct Comma Separated Values [message #653440 is a reply to message #653429]

Tue, 05 July 2016 10:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select listagg(word,',') within group (order by null) res
  2  from ( select distinct regexp_substr(val, '[^,]+', 1, column_value) word
  3         from (select 'TESTER,DBA,DBA,DBA,DEVELOPER,DEVELOPER,TESTER,DBA,DBA,DEVELOPER' val
  4               from dual),
  5              table(cast(multiset(select level from dual
  6                                  connect by level <= regexp_count(val,',')+1)
  7         as sys.odciNumberList)) )
  8  /
RES
----------------------------------------------------------------------------------------------
DBA,DEVELOPER,TESTER

Report message to a moderator

Re: Distinct Comma Separated Values [message #653442 is a reply to message #653440]

Tue, 05 July 2016 13:13

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Another try:

SQL> with t(col) as (select 'TESTER,DBA,DBA,DBA,DEVELOPER,DEVELOPER,TESTER,DBA,DBA,DEVELOPER' from dual),
  2  t_cnt(cnt) as (select regexp_count(col,',') from t),
  3  --select * from t_cnt
  4  t_unpvt(col1,n) as (
  5  select substr(col,1,instr(col,',',1,1)-1),0 from t
  6  union all
  7  select substr(col,instr(col,',',1,n+1)+1,instr(col,',',1,n+2)-instr(col,',',1,n+1)-1),n+1 from t,t_unpvt,t_cnt where n+1 < t_cnt.cnt
  8  ),
  9  t_fin(col2) as (
 10  select distinct col1 from t_unpvt )
 11  select listagg(  col2,',') WITHIN GROUP (ORDER BY col2) from t_fin
 12  /
 
LISTAGG(COL2,',')WITHINGROUP(O
--------------------------------------------------------------------------------
DBA,DEVELOPER,TESTER
 
SQL>

Report message to a moderator

Re: Distinct Comma Separated Values [message #653443 is a reply to message #653442]

Tue, 05 July 2016 13:36

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

live4learn wrote on Tue, 05 July 2016 14:13

Another try:

Try it yourself for:

with t(col) as (select 'TESTER' from dual)

or

with t(col) as (select 'TESTER,DBA,DEVELOPER' from dual)

or any string where last value is unique. Anyway, just to fix immediate issues:

with t(col) as (select '1,2,3' from dual),
t_cnt(cnt) as (select regexp_count(col,',') + 1 from t),
--select * from t_cnt
t_unpvt(col1,n) as (
select substr(col,1,instr(col || ',' ,',',1,1)-1),0 from t
union all
select substr(col,instr(col || ',',',',1,n+1)+1,instr(col || ',',',',1,n+2)-instr(col || ',',',',1,n+1)-1),n+1 from t,t_unpvt,t_cnt where n+1 < t_cnt.cnt
),
t_fin(col2) as (
select distinct col1 from t_unpvt )
select listagg( col2,',') WITHIN GROUP (ORDER BY col2) from t_fin
/

SY.

Report message to a moderator

Re: Distinct Comma Separated Values [message #653444 is a reply to message #653443]

Tue, 05 July 2016 14:02

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Assuming values have no spaces:

with t(col) as (select 'TESTER,DBA,DBA,DBA,DEVELOPER,DEVELOPER,TESTER,DBA,DBA,DEVELOPER' from dual)
select  replace(column_value,' ',',') distinct_col
  from  t,
        xmltable (('xs:string(distinct-values(("' || replace(col,',','","') || '")))'))
/
DISTINCT_COL
--------------------
TESTER,DBA,DEVELOPER

SQL>

Otherwise:

with t(col) as (select 'TESTER,DBA,DBA,DBA,DEVELOPER,DEVELOPER,TESTER,DBA,DBA,DEVELOPER' from dual)
select  distinct_col
  from  t,
        xmltable(
                 'string-join(distinct-values(ora:tokenize($str,",")),",")'
                 passing ',' || t.col as "str"
                 columns distinct_col clob path '.'
                ) x
/
DISTINCT_COL
--------------------
DBA,DEVELOPER,TESTER

SQL>

SY.

Report message to a moderator