WITH t AS ( SELECT 1 Column_ID, 'A,B,C,D,E' Column_B FROM dual UNION ALL SELECT 2, 'A,A,B,D' FROM dual UNION ALL SELECT 3, 'D,B,C,B,F' FROM dual ) -- 1. 값의 길이가 고정인 경우만 존재한다면? SELECT column_id, column_b FROM t WHERE REGEXP_LIKE(column_b, '([^,]+).*\1') ;
WITH t AS ( SELECT 1 Column_ID, 'A,B,C,D,E' Column_B FROM dual UNION ALL SELECT 2, 'A,A,B,D' FROM dual UNION ALL SELECT 3, 'D,B,C,B,F' FROM dual UNION ALL SELECT 3, 'A,B,C,D,CC,CCC' FROM dual -- 요런거 ) -- 값의 길이가 가변일 경우가 존재한다면? SELECT column_id, column_b FROM t WHERE REGEXP_LIKE(','||column_b||',', '(,[^,]+)(,[^,]+)*\1,') ;
with t (Column_ID,Column_B) as ( select 1,'A,B,C,D,E' from dual union all select 2,'A,A,B,D' from dual union all select 3,'D,B,C,B,F' from dual ) select column_id from ( select column_id,column_b , row_number() over(partition by column_id,column_b,rs order by rs )rn from ( select column_id,column_b, regexp_substr(column_b,'[^,]+',1,lv) rs from ( select column_id, column_b, regexp_count(column_b,',') rc,lv from t,(select level lv from dual connect by level <=5)) where rc + 1 >= lv)) where rn = 2;
WITH t AS ( SELECT 1 Column_ID, 'A,B,C,D,E' Column_B FROM dual UNION ALL SELECT 2, 'A,A,B,D' FROM dual UNION ALL SELECT 3, 'D,B,C,B,F,CC,CCC' FROM dual ) SELECT Column_ID , COUNT(B) CNT FROM (SELECT Column_ID , REGEXP_SUBSTR (Column_B, '[^,]+', 1, LV) B FROM T , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 99) WHERE REGEXP_COUNT(Column_B,',')+1 >= LV ) GROUP BY Column_ID , B HAVING COUNT(B) > 1