꿈꾸는 개발자, DBA 커뮤니티 구루비

제외단어를 제거한 후 값에 포함단어가 존재하는지 확인하는 쿼리

by 동희 [SQL Query] oracle12c [2023.08.22 15:18:51]

안녕하세요..오라클 12C를 사용 중입니다.

제외단어를 제거한 후 값에 포함단어가 존재하는지 여부를 체크 하려고 하는데요..

WITH 
IN_TXT AS ( /*포함단어*/
    SELECT '사과' TXT FROM DUAL UNION ALL
    SELECT '바나나' TXT FROM DUAL
),
EX_TXT AS ( /*제외단어*/
    SELECT '맛 없는 사과' TXT FROM DUAL UNION ALL
    SELECT '노랑 바나나' TXT FROM DUAL
),
VAL_TXT AS ( /*체크대상*/
    SELECT 1 RN, '맛 있는 사과도 있고 맛 없는 사과도 있다.' VAL FROM DUAL UNION ALL
    SELECT 2 RN, '맛 없는 사과도 있고 노랑 바나나도 있고 포도도 있다.' VAL FROM DUAL UNION ALL
    SELECT 3 RN, '포도도 있다.' VAL FROM DUAL
)
SELECT
    V.RN,
    V.VAL,
    I.TXT I_TXT,
    E.TXT E_TXT
FROM VAL_TXT V
LEFT JOIN EX_TXT E ON V.VAL LIKE '%' || E.TXT || '%'
LEFT JOIN IN_TXT I ON REPLACE(REPLACE(V.VAL, '.', ''), E.TXT, '') LIKE '%' || I.TXT || '%'
;

체크대상(VAL_TXT)은 전체 가져오고 그 체크대상(VAL_TXT)의 값(VAL)의 내용에서 제외단어(EX_TXT)를 지우고 나서 포함단어(IN_TXT)의 내용이 존재 하는지

With문을 새로 추가 하거나 하지 않고 체크해 보려고 하는데 잘 안되네요..

원하는 결과는 다음과 같습니다.

RN	VAL	CHK_YN
1	맛 있는 사과도 있고 맛 없는 사과도 있다.	Y
2	맛 없는 사과도 있고 노랑 바나나도 있고 포도도 있다.	N
3	포도도 있다.	N

이게 가능 할까요?? 도움 부탁드립니다..

by 마농 [2023.08.22 16:29:45]

WITH in_txt AS
( /*포함단어*/
SELECT '사과' txt FROM dual
UNION ALL SELECT '바나나' FROM dual
)
, ex_txt AS
( /*제외단어*/
SELECT '맛 없는 사과' txt FROM dual
UNION ALL SELECT '노랑 바나나' FROM dual
)
, val_txt AS
( /*체크대상*/
SELECT 1 rn, '맛 있는 사과도 있고 맛 없는 사과도 있다.' val FROM dual
UNION ALL SELECT 2, '맛 없는 사과도 있고 노랑 바나나도 있고 포도도 있다.' FROM dual
UNION ALL SELECT 3, '포도도 있다.' FROM dual
)
SELECT rn
     , val
     , CASE WHEN REGEXP_LIKE(REGEXP_REPLACE(val, x), n) THEN 'Y' ELSE 'N' END chk_yn
  FROM val_txt
     , (SELECT LISTAGG(txt, '|') WITHIN GROUP(ORDER BY 1) x FROM ex_txt)
     , (SELECT LISTAGG(txt, '|') WITHIN GROUP(ORDER BY 1) n FROM in_txt)
;

by 동희 [2023.08.22 16:32:04]

아아...마농님 정말 감사드립니다..ㅠㅠ

by 동희 [2023.08.24 08:19:45]

마농님 listagg를 이용하여 정규식의 값을 만든다는건 정말 획기적이네요...감탄합니다..

그런데 문득 만약 4000byte가 넘는다면 어떻게 해야하나 라는 생각을 하게 되었는데요.....

그럴 경우 결국은 함수를 만들어서 사용할 수 밖에 없게 되겠죠??

by 마농 [2023.08.24 08:59:34]

현재의 상황을 정확하게 묘사해 주세요.
제외단어, 추출단어의 레코드수, 합쳤을 때 전체 길이, 레코드 당 평균 길이 등.
조건은 가변인지 고정인지? 4000Byte 초과 가능성이 정말 있는 것인지? 등등

by 동희 [2023.08.24 09:38:53]

마농님 답글 감사드립니다..

포함단어, 제외단어는 사용자가 등록할 수 있는 가변자료이며, 단어의 칼럼 사이즈는 varchar2(300) 으로 되어 있습니다.

체크대상 의 칼럼 사이즈는 varchar2(4000) 입니다.

단어를 사용자가 등록할 수 있으므로 4000byte가 안넘는다는 보장을 할 수 가 없어서요..

by 마농 [2023.08.24 10:15:59]

글쎄요? 막연하게 넘을 것이다? 라고 걱정하지 마시고.
업무적인 특성과 입력 값들의 특성 등을 검토해 보세요.
컬럼의 최대 사이즈 보다는 실제 입력값들의 사이즈를 확인해 보세요.
그리고 나서 정말 넘을 가능성이 크다라고 생각되시면?제가 요청한 정보들을 주세요.

by 마농 [2023.08.24 11:13:03]

WITH in_txt AS
( /*포함단어*/
SELECT '사과' txt FROM dual
UNION ALL SELECT '바나나' FROM dual
)
, ex_txt AS
( /*제외단어*/
SELECT '맛 없는 사과' txt FROM dual
UNION ALL SELECT '노랑 바나나' FROM dual
)
, val_txt AS
( /*체크대상*/
SELECT 1 rn, '맛 있는 사과도 있고 맛 없는 사과도 있다.' val FROM dual
UNION ALL SELECT 2, '맛 없는 사과도 있고 노랑 바나나도 있고 포도도 있다.' FROM dual
UNION ALL SELECT 3, '포도도 있다.' FROM dual
)
, tmp1 AS
(
SELECT a.rn, a.val, b.txt
     , COUNT(*)     OVER(PARTITION BY rn) cnt
     , ROW_NUMBER() OVER(PARTITION BY rn ORDER BY 1) seq
  FROM val_txt a
     , ex_txt  b
)
, tmp2(rn, val, cnt, seq, x) AS
(
SELECT rn, val, cnt
     , seq
     , REPLACE(val, txt) x
  FROM tmp1
 WHERE seq = 1
 UNION ALL
SELECT p.rn, p.val, p.cnt
     , c.seq
     , REPLACE(p.x, c.txt) x
  FROM tmp2 p
     , tmp1 c
 WHERE c.rn  = p.rn
   AND c.seq = p.seq + 1
)
SELECT rn
     , val
     , CASE WHEN EXISTS (SELECT 1 FROM in_txt WHERE INSTR(m.x, txt) > 0)
            THEN 'Y' ELSE 'N' END chk_yn
  FROM tmp2 m
 WHERE cnt = seq
;

by 동희 [2023.08.24 18:15:55]

마농님 답변 정말 감사드립니다....ㅠㅠ

데이터베이스 질문과 답변