CODEWITHSUNDEEP
sqloracle-database
Abhishek Kapoor
Abhishek Kapoor

Reputation: 25

Distinct Comma separated values in oracle

I have a string as '1,1,2,3,4,4,5,6,6,7' stored in a column. I need distinct comma separated value as output using sql query.

e.g. For given string output should be '1,2,3,4,5,6,7'. No duplicacy persists in output.

Upvotes: 2

Views: 9226

Answers (5)

Adarsh Joshi
Adarsh Joshi

Reputation: 49

SELECT 
  listagg(ra,',')  WITHIN GROUP (ORDER BY ra)
FROM
(
  SELECT
  DISTINCT (REGEXP_SUBSTR('02,02,02,02,02,03,04,03', '[^,]+', 1, LEVEL) )ra
  FROM DUAL
  CONNECT BY REGEXP_SUBSTR('02,02,02,02,02,03,04,03', '[^,]+', 1, LEVEL) IS 
  NOT NULL
);

Upvotes: 0

likhith
likhith

Reputation: 44

According my point of view:

select wm_concat(distinct substr(replace('1,1,2,3,4,4,5,6,6,7',',',''),level,1)) as out     
from dual connect by level <= length('1,1,2,3,4,4,5,6,6,7');

Upvotes: 0

arunb2w
arunb2w

Reputation: 1196

We can do this using regex_substr and connect by. Please try this.

 select distinct num from
(SELECT REGEXP_SUBSTR('1,1,2,3,4,4,5,6,6,7','[^,]+',1,level) as num
FROM DUAL 
CONNECT BY LEVEL<= LENGTH(REGEXP_REPLACE('1,1,2,3,4,4,5,6,6,7','[^,]','')));

Without regex: After some clarification in the question

 with t as (SELECT distinct substr(replace('1,1,2,3,4,4,5,6,6,7',','),level,1)||',' as num
 FROM DUAL
 CONNECT BY LEVEL<=  LENGTH( '1,1,2,3,4,4,5,6,6,7' ) - LENGTH(REPLACE( '1,1,2,3,4,4,5,6,6,7' , ','))+1)
 select listagg(num) within group (order by num) from t;

Upvotes: 0

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40603

Try

select
  regexp_replace('1,1,2,3,4,4,5,6,6,7', '([^,]+),\1', '\1')
from 
   dual;

However, this wont work if your input string contains a figure more than twice. If this bothers you, you might want to try

select
  regexp_replace('1,1,2,3,4,4,4,5,6,6,6,6,6,6,7', '([^,]+)(,\1)+', '\1')
from dual;

Upvotes: 0

Dibstar
Dibstar

Reputation: 2364

without regexp:

WITH t AS
( SELECT '1,2,3,3,3,4,5,6,7,7,7,7' AS num FROM dual
)
SELECT DISTINCT
       SUBSTR (
         num
       , instr(num, ',', 1, level) + 1
       , instr(num, ',', 1, level + 1) - instr(num, ',', 1, level) - 1)
        AS numbers
FROM (select ','||num||',' num from t)
CONNECT BY level <= length(num) - length(replace(num,',')) -1

with regexp:

SELECT DISTINCT REGEXP_SUBSTR( '1,1,2,3,4,4,5,6,6,7' , '[^,]+', 1, lvl)
FROM DUAL, 
(SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= LENGTH( '1,1,2,3,4,4,5,6,6,7' ) - LENGTH(REPLACE( '1,1,2,3,4,4,5,6,6,7' , ','))+1)
WHERE lvl <= LENGTH( '1,1,2,3,4,4,5,6,6,7' ) - LENGTH(REPLACE( '1,1,2,3,4,4,5,6,6,7' , ',')) + 1

Upvotes: 3

Related Questions