PostgreSQL LIKE 여러개 - PostgreSQL LIKE yeoleogae

Question

이번에 복잡한 쿼리를 많이 사용해서 mybatis로 개발을 했다.

쿼리를 이것저것 작성을 하다보니 like 문을 or로 묶어서 사용하게 됐는데, like 문 여러 개를 or로 묶다 보니 이걸 묶어서 한꺼번에 쉽게 표현할 수 있는 방법이 없을까 찾아보다가 Similar to라는 것을 알게 되었다.

참고로 이는 다른 DBMS에서 regex 조건을 사용하는 것과 같은 효과를 낸다.

예를 들어 like 문으로 아래와 같이 '가', '나', '다'로 시작하는 문자를 찾는다고 하자.

그럴 경우 like문으로 쿼리를 작성하면 아래와 같다.

SELECT *
FROM table
WHERE name like '가%' or name like '나%' or name like '다%';

만일 여기에 다른 조건이 붙거나 '가', '나', '다'까지가 아닌 '라', '마'... 등 or 문으로 똑같은 name에 대한 조건을 붙여야할게 있다면 like 문을 더해서 쿼리가 길어지고 복잡해진다. 이렇게 될 경우 나중에 쿼리를 수정하기도 번거롭고 쿼리를 테스트하기도 번거롭다.

하지만 similar to를 사용하면 아래와 같이 조건문을 여러 개의 or 조건이 아닌 하나의 조건으로 끝낼 수 있다.

SELECT *
FROM table
WHERE name similar to '(가|나|다)%'

조건문을 보면 아래와 같이 similar to 뒤에 정규식을 사용해 그 안에서 or 조건을 수행할 수 있다.

마치 다른 DBMS, 예를 들어 MySql에서 regex라는 것을 사용해 조건을 작성한 것과 같은 효과를 낼 수 있다.

similar to 뒤에 들어가는 정규식은 여느 정규식과 똑같이 작성하면 된다.

...
WHERE name similar to '(가|나|다)%'

각 DBMS마다 쿼리가 비슷비슷하다고는 하지만 역시 여러 가지 개발 언어처럼 쿼리 작성법 또한 각 DBMS마다 조금씩 차이가 있는 것 같다.

🤚 PostgreSql Pattern Matching 공식 문서

https://www.postgresql.org/docs/12/functions-matching.html

9.7. Pattern Matching

9.7. Pattern Matching 9.7.1. LIKE 9.7.2. SIMILAR TO Regular Expressions 9.7.3. POSIX Regular Expressions There are three separate approaches to pattern …

www.postgresql.org

select *
  from table
 where name like '최%'
    or name like '김%' ;

구글링 해보니 이런 구문들이 많던데 ..

해당 쿼리는 중복값이 나오는 에러가 있다.

정규식으로 해결

select *
  from table
 where name ~ '^(최|김).*';

~ : 일치하는 것 ( like )

!~ : 일치하지 않는 것 ( not like )

오라클에선

select *
  from table
 where regexp_like (name, '^최|^김');

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 여러개의 문자열을 'OR' 조건으로 매칭하는 3가지 SQL 방법을 소개하겠습니다.

(1) LIKE '%string1%' OR LIKE '%string2%' ...

(2) ANY(ARRAY['%string1%', '%string2%', ...])

(3) regular expression matching: ~ '(string1|string2|...)'

먼저 예제로 사용할 샘플 테이블을 만들어보겠습니다. 과일가게에서 장바구니 ID별로 구매한 과일 품목이 문자열로 들어있는 테이블입니다.

-- create a sample table
DROP TABLE IF EXISTS basket_tbl;
CREATE TABLE basket_tbl (
	id int
	, item text
);


INSERT INTO basket_tbl VALUES 
(1, 'orange, apple, grape')
, (2, 'guava, apple, durian')
, (3, 'strawberry, lime, leomon')
, (4, 'mango, mangosteen, plum')
, (5, 'plum, guava, peach');

SELECT * FROM basket_tbl ORDER BY id;
--id|item                    |
----+------------------------+
-- 1|orange, apple, grape    |
-- 2|guava, apple, durian    |
-- 3|strawberry, lime, leomon|
-- 4|mango, mangosteen, plum |
-- 5|plum, guava, peach      |

위의 샘플 테이블의 item 칼럼의 문자열에서 'apple', 'orange', 'peach' 중에 하나라도(OR) 문자열이 매칭(string matching)이 되면 SELECT 문으로 조회를 해오는 SQL query 를 3가지 방법으로 작성해보겠습니다.

(1) LIKE '%string1%' OR LIKE '%string2%' ...

가장 단순한 반면에, 조건절 항목이 많아질 경우 SQL query 가 굉장히 길어지고 비효율적인 단점이 있습니다.

-- (1) multiple LIKE '%string1%' OR LIKE '%string2%' OR...
SELECT * 
FROM basket_tbl 
WHERE item LIKE '%apple%' 
	OR item LIKE '%orange%'
	OR item LIKE '%peach%'
ORDER BY id;

--id|item                |
----+--------------------+
-- 1|orange, apple, grape|
-- 2|guava, apple, durian|
-- 5|plum, guava, peach  |

(2) ANY(ARRAY['%string1%', '%string2%', ...])

문자열 매칭 조건절의 각 문자열 항목을 ARRAY[] 에 나열을 해주고, any() 연산자를 사용해서 이들 문자열 조건 중에서 하나라도 매칭이 되면 반환을 하도록 하는 방법입니다. 위의 (1)번 보다는 SQL query 가 짧고 깔끔해졌습니다.

-- (2) ANY(ARRAY['%string1%', '%string2%',...])
SELECT * 
FROM basket_tbl 
WHERE item LIKE ANY(ARRAY['%apple%', '%orange%', '%peach%'])
ORDER BY id;

--id|item                |
----+--------------------+
-- 1|orange, apple, grape|
-- 2|guava, apple, durian|
-- 5|plum, guava, peach  |

(3) regular expression matching: ~ '(string1|string2|...)'

마지막으로, 정규표현식(regular expression) '~'을 이용해서 복수의 문자열을 OR 조건(수직바 '|')으로 매칭하는 방법입니다. '%'를 사용하지 않아도 되므로 (1), (2) 와 비교했을 때 가장 SQL query 가 간단한 방법입니다.

-- (3) regular expression match: ~ '(string1|string2|...)'
SELECT * 
FROM basket_tbl 
WHERE item ~ '(apple|orange|peach)'
ORDER BY id;

--id|item                |
----+--------------------+
-- 1|orange, apple, grape|
-- 2|guava, apple, durian|
-- 5|plum, guava, peach  |

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요~! :-)