Home » SQL & PL/SQL » SQL & PL/SQL » Distinct Comma Separated Values (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Distinct Comma Separated Values [message #653427] Tue, 05 July 2016 04:43 Go to next message
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
Re: Distinct Comma Separated Values [message #653429 is a reply to message #653427] Tue, 05 July 2016 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Distinct Comma Separated Values [message #653440 is a reply to message #653429] Tue, 05 July 2016 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Distinct Comma Separated Values [message #653442 is a reply to message #653440] Tue, 05 July 2016 13:13 Go to previous messageGo to next message
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> 
Re: Distinct Comma Separated Values [message #653443 is a reply to message #653442] Tue, 05 July 2016 13:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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.
Re: Distinct Comma Separated Values [message #653444 is a reply to message #653443] Tue, 05 July 2016 14:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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.
Re: Distinct Comma Separated Values [message #653464 is a reply to message #653427] Wed, 06 July 2016 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Distinct Comma Separated Values [message #653467 is a reply to message #653464] Wed, 06 July 2016 13:06 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
I love how there are a dozen ways to do the exact same thing in oracle. Very cool
Re: Distinct Comma Separated Values [message #653493 is a reply to message #653467] Thu, 07 July 2016 11:53 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
Thanks SY .
Re: Distinct Comma Separated Values [message #656202 is a reply to message #653493] Wed, 28 September 2016 06:15 Go to previous message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thank you all.
Previous Topic: Fixed Width Flat file via Oracle table
Next Topic: Multiple inline query
Goto Forum:
  


Current Time: Fri Feb 23 10:50:19 CST 2018

Total time taken to generate the page: 0.02716 seconds