[Oracle] 오라클 여러개(다중) LIKE 방법 (REGEXP_LIKE)

오라클에서 여러 개의 단어를 LIKE로 검색하기 위해서는 동적 쿼리를 사용하거나 LIKE를 OR로 묶어서 사용했다.  
Oracle 10g부터 정규식 함수가 추가 되었으며 그 중에서 REGEXP_LIKE 함수를 사용하여 다중 검색을 쉽게 할 수 있게 되었다. 

기본 사용법

WITH temp_table AS (
   SELECT 'Samsung Galaxy Note 4' text FROM dual UNION ALL
   SELECT 'Apple iPhone 6 Plus' text   FROM dual UNION ALL
   SELECT 'Samsung Galaxy text' text   FROM dual UNION ALL
   SELECT 'Apple iPhone 6' text        FROM dual UNION ALL
   SELECT 'LG G3' text                 FROM dual UNION ALL
   SELECT 'SonyXperia Z3' text         FROM dual UNION ALL
   SELECT 'Motorola Moto G' text       FROM dual UNION ALL
   SELECT 'HTC One M8' text            FROM dual UNION ALL
   SELECT 'Nokia Lumia 930' text       FROM dual
)
SELECT *
  FROM temp_table
 WHERE REGEXP_LIKE(text, 'Samsung|Apple|Nokia')

 

 

검색할 단어를 파이프( | )로 연결하여 하나의 문자열로 만든 후 사용하면 된다.

 

 

대소문자 구분없이 검색

WITH temp_table AS (
   SELECT 'Samsung Galaxy Note 4' text FROM dual UNION ALL
   SELECT 'Apple iPhone 6 Plus' text   FROM dual UNION ALL
   SELECT 'Samsung Galaxy text' text   FROM dual UNION ALL
   SELECT 'Apple iPhone 6' text        FROM dual UNION ALL
   SELECT 'LG G3' text                 FROM dual UNION ALL
   SELECT 'SonyXperia Z3' text         FROM dual UNION ALL
   SELECT 'Motorola Moto G' text       FROM dual UNION ALL
   SELECT 'HTC One M8' text            FROM dual UNION ALL
   SELECT 'Nokia Lumia 930' text       FROM dual
)
SELECT *
  FROM temp_table
 WHERE REGEXP_LIKE(text, 'g3|sony|htc', 'i')

 

 

대문자, 소문자의 구분 없이 조회하고 싶다면 세 번째 인자에 “i”를 넣어주면 된다.

[Oracle] 오라클 REGEXP_LIKE 함수 사용법