10.21 TIL : SQL 필터를 통한 데이터 정제

DB에서 varchar로 저장된 json String에 대해 한글인 경우의 데이터를 추출해야 했다. 다만, 한글이어도 상관없는 키와 한글 데이터가 같이 섞여있기 때문에, 이를 제외하는 것이 중요했다

 

완성된 쿼리 :

WITH W1 AS (
    -- 1. 본 테이블에서 한글이 포함된 모든 데이터 조회
    SELECT
        sequence,
        content
    FROM
        board b
    WHERE
        b.content RLIKE '[가-힣]'
),
W2 AS (
    SELECT
    	sequence,
    	REGEXP_SUBSTR(
        	b.content
        	-- 패턴: "key":" 뒤에 오며, 한글을 포함하고, 닫는 따옴표 직전까지의 내용을 캡처
        	'(?<="key":")([^"]*[가-힣]+[^"]*)' 
    	) AS Extracted_Korean_Content,
    	b.content
	FROM
    	board b
	WHERE
    	-- content에 "key" 속성 값 중 한글이 포함된 블록이 없는 행을 조회
    	b.content NOT REGEXP '("key1":"|"key2":"|"key3":"|"key4":")[^"]*[가-힣]+'
)
SELECT
W1.*, W2.Extracted_Korean_Content
FROM W1 INNER JOIN W2 ON W1.sequence = W2.sequence
ORDER BY W1.sequence DESC

 

*REGXP_SUBSTR() 메서드에서 패턴이 있는 값을 추출한다

 

W1 테이블은 한글이 포함된 모든 데이터를 조회하고,

W2 테이블에서 특정 키 뒤에 한글이 오는 경우를 제외 한 후

 

이너 조인을 통해 내가 원하는 키에 한글이 있는 경우만 추출할 수 있다