SQL LIKE 대신 정규식 사용한 검색 방법: sql multiple like

특정 키워드가 포함된 상품의 주문 수량을 구하는 경우가 많습니다.

키워드가 적은 경우에는 like or을 사용하거나 정규식을 사용합니다.

--like or 사용하는 경우
select
	item_id,
	item_name,
	item_amount
from
	items
where
	item_name like '핸드폰 케이스'
	or item_name like '에어팟 케이스'
	or item_name like '원피스'
;

--정규식 사용하는 경우(postgresql)
select
	item_id,
	item_name,
	item_amount
from
	items
where
	item_name ~* '핸드폰 케이스|에어팟 케이스|원피스'

하지만 키워드가 많으면 where절에서 키워드를 늘리기가 어렵습니다.

 

이런 경우에는 테이블 조인을 이용하는 방법이 있습니다.

우선 아래 코드로 데이터로 items 테이블을 만듭니다. 예시 데이터입니다.

CREATE TABLE items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(255) NOT NULL,
    item_amount INT NOT NULL
);

INSERT INTO items (item_id, item_name, item_amount)
VALUES
    (1, '아이폰 13 프로 마블 그래픽 핸드폰 케이스', 100),
    (2, '디즈니 미키마우스 에어팟 케이스', 80),
    (3, 'KF94 대형 화이트 마스크 20개입', 300),
    (4, '여름용 러플 레이스 브이넥 원피스', 120),
    (5, '나이키 에어맥스 270 리액트 운동화', 200),
    (6, '캠핑용 접이식 의자', 50),
    (7, '32인치 LED TV', 40),
    (8, '스테인리스 물병 500ml', 250),
    (9, '블루투스 키보드', 150),
    (10, '진공 통영굴 1kg', 30)
;

테이블을 실행하면 아래와 같습니다.

Items 테이블

Items 테이블에서 아래 키워드들이 포함된 아이템 이름을 확인하려면 아래 쿼리를 사용하면 됩니다.

• 핸드폰 케이스, 에어팟 케이스, 마스크, 원피스, 운동화

with keyword_list(keyword) as (
    values
        ('핸드폰 케이스'),
        ('에어팟 케이스'),
        ('마스크'),
        ('원피스'),
        ('운동화')
)
select
    k.keyword,
    i.item_id,
    i.item_name,
    i.item_amount
from
    items as i
join
    keyword_list as k
on
    --presto sql 사용 시 아래 쿼리 사용
    regexp_like(lower(i.item_name),concat('\b',lower(k.keyword),'\b'))
    --postgre sql 사용 시 아래 쿼리 사용
    --lower(i.item_name) ~ concat('\m', lower(k.keyword), '\M')

위 쿼리는 keyword_list 테이블에 있는 키워드가 items 테이블의 아이템 이름에 포함되어 있는지 확인합니다.

위와 같이 키워드 임시 테이블을 만들어도 되고, 키워드가 많은 경우는 테이블로 ingest 한 후에 사용하시면 됩니다.

 

쿼리의 concat('\b', lower(k.keyword), '\b')에서 \b는 단어 경계를 의미하는 정규식 메타 문자입니다.

이를 사용하여 키워드의 앞뒤에 공백, 구두점 등이 있는지 확인하고, 정확한 단어만 일치시킬 수 있습니다.

쿼리 결과

위와 같이 키워드가 포함된 아이템들을 확인할 수 있습니다. 

위 방법은 찾아야 할 키워드 개수가 많으면 더욱더 유용합니다. 예를 들어 1000개의 키워드가 어떤 item_name에 포함되어 있는지를 확인하는 경우는 위와 같은 방법이 훨씬 효율적입니다.