개요
- MySQL 8.0 부터는 Regular Expression에 대한 String function 이 추가로 제공된다.
- REGEXP_REPLACE() : Regular Expression 으로 검색된 부분에 대해 정해진 값으로 치환한다.
- REGEXP_INSTR() : Regular Expression 으로 검색된 부분의 문자의 위치를 나타낸다.
- REGEXP_LIKE() : Regular Expression 으로 검색된 부분의 유무에 따라 TRUE, FALSE (1,0) 를 반환한다.
- REGEXP_SUBSTR() : 특정위치부터 Regular Expression 으로 검색된 부분의 횟수를 통해 결과를 반환한다.
사용예제
REGEXP_REPLACE
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_REPLACE('010-2446-0111','[0-9]','*');
+---------------------------------------------+
| REGEXP_REPLACE('010-2446-0111','[0-9]','*') |
+---------------------------------------------+
| ***-****-**** |
+---------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중, 숫자를 찾게 되면, '*' 로 치환해라.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_REPLACE('010-2446-0111','[$&+,:;=?@#|<>.^*()%!-]','');
+--------------------------------------------------------------+
| REGEXP_REPLACE('010-2446-0111','[$&+,:;=?@#|<>.^*()%!-]','') |
+--------------------------------------------------------------+
| 01024460111 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중, 특수문자를 모두 제거해라.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_REPLACE('010-2446-0111','[0-9]','*',10);
+------------------------------------------------+
| REGEXP_REPLACE('010-2446-0111','[0-9]','*',10) |
+------------------------------------------------+
| 010-2446-**** |
+------------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중, 10번째 자리 이후부터는 모두 '*' 로 치환해라.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_REPLACE('010-2446-0111','[0-9]','*',10,3);
+--------------------------------------------------+
| REGEXP_REPLACE('010-2446-0111','[0-9]','*',10,3) |
+--------------------------------------------------+
| 010-2446-01*1 |
+--------------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중, 10번째 자리 이후부터는 숫자로 된 값을 찾아 3번째 값을 '*' 로 치환해라.
REGEXP_INSTR
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)','[가-힣]');
+----------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)','[가-힣]') |
+----------------------------------------------------------+
| 1 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중 한글은 첫번째 자리에서 확인된다.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)','[가-힣]',3);
+------------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)','[가-힣]',3) |
+------------------------------------------------------------+
| 9 |
+------------------------------------------------------------+
1 row in set (0.01 sec)
--^^ 3번째 이후 데이터중 한글은 9번째 자리에서 확인된다.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Za-z]');
+---------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Za-z]') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
--^^ 데이터중 알파벳은 네번째 자리에서 확인된다.
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Z]');
+------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Z]') |
+------------------------------------------------------+
| 4 |
+------------------------------------------------------+
1 row in set (0.00 sec)
--^^ korea는 대문자가 아님에도 불구하고 네번째 자리의 결과를 나타낸다.
mysql 8.0 [localhost] {msandbox} ((none)) > select @@session.collation_connection;
+--------------------------------+
| @@session.collation_connection |
+--------------------------------+
| utf8mb4_0900_ai_ci |
+--------------------------------+
1 row in set (0.00 sec)
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)' COLLATE utf8mb4_bin,'[A-Z]');
+--------------------------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)' COLLATE utf8mb4_bin,'[A-Z]') |
+--------------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
--^^ 이유는 위와 같이 현재의 default collation이 Case Insensitive (utf8mb4_0900_ai_ci) 이기 때문이다.
mysql 8.0 [localhost] {msandbox} ((none)) > set names utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
mysql 8.0 [localhost] {msandbox} ((none)) > select @@session.collation_connection;
+--------------------------------+
| @@session.collation_connection |
+--------------------------------+
| utf8mb4_bin |
+--------------------------------+
1 row in set (0.00 sec)
mysql 8.0 [localhost] {msandbox} ((none)) > SELECT REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Z]');
+------------------------------------------------------+
| REGEXP_INSTR('한국 korea홧팅 (1센터)','[A-Z]') |
+------------------------------------------------------+
| 0 |
+------------------------------------------------------+
1 row in set (0.00 sec)
--^^ Collation을 Case Sensitive (utf8mb4_bin) 로 변경하면, 원하던 정상적인 결과가 나타난다.
REGEXP_LIKE
-- 위의 3개의 CASE 와는 달리 비교적 WHERE 구문에 자주 쓰일 수 있는 function이다. 이미 이전 버젼에서도 regexp 혹은 rlike 구문을 통해 사용가능했다.
-- 테스트를 위해 '010-2446-0111' 와 '010-2446-0113' 를 갖는 임시테이블 cte를 생성한다.
mysql 8.0 [localhost] {msandbox} ((none)) > WITH cte (phone_number) AS
-> (SELECT '010-2446-0111' UNION ALL SELECT '010-2446-0113')
-> SELECT * FROM cte WHERE REGEXP_LIKE (phone_number, '[1]{3}');
+---------------+
| phone_number |
+---------------+
| 010-2446-0111 |
+---------------+
1 row in set (0.00 sec)
--^^ '1'이라는 숫자가 연속 3번 들어간 전화번호를 패턴매칭을 이용하여 찾는다.
mysql 8.0 [localhost] {msandbox} ((none)) > WITH cte (phone_number) AS
-> (SELECT '010-2446-0111' UNION ALL SELECT '010-2446-0113')
-> SELECT * FROM cte WHERE phone_number REGEXP '[1]{3}';
+---------------+
| phone_number |
+---------------+
| 010-2446-0111 |
+---------------+
1 row in set (0.00 sec)
--^^ 이는 예전버젼에서 REGEXP 혹은 RLIKE를 통해 패턴매칭을 이용할 수 있었다.
mysql 8.0 [localhost] {msandbox} ((none)) > WITH cte (phone_number) AS
-> (SELECT '010-2446-0111' UNION ALL SELECT '010-2446-0113')
-> SELECT * FROM cte WHERE NOT REGEXP_LIKE (phone_number, '[1]{3}');
+---------------+
| phone_number |
+---------------+
| 010-2446-0113 |
+---------------+
1 row in set (0.01 sec)
--^^ NOT 구문을 통해 '1'이 3번 연속 나오지 않는 전화번호를 찾을 수도 있다.