MySQL REGEXP 사용법 - MySQL REGEXP sayongbeob

mysql에서 정규표현식은 크게 2가지로 나뉩니다.
1. select 절에서의 사용 (regexp_replace)
2. where 절에서의 사용 (regexp_like)

결국 검색용으로 정규식을 쓸것인지 조회값을 변경하기 위해 쓸것인지로 보면 되겠죠
하지만 위에서 언급한 두 함수 regexp_replace와 regexp_like의 경우 mysql 8 이상 부터에서만 사용가능 합니다.
이전 버전에서는 사용할 수 없기 때문에 다른 방법을 사용해야 된다는 말이죠

1. regexp_replace
regexp_replace의 경우에는 함수를 직접 만들어 주면 됩니다.
참고 : https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/

DELIMITER $$
CREATE FUNCTION  `regexp_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))

RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN 
 DECLARE temp VARCHAR(1000); 
 DECLARE ch VARCHAR(1); 
 DECLARE i INT;
 SET i = 1;
 SET temp = '';
 IF original REGEXP pattern THEN 
  loop_label: LOOP 
   IF i>CHAR_LENGTH(original) THEN
    LEAVE loop_label;  
   END IF;
   SET ch = SUBSTRING(original,i,1);
   IF NOT ch REGEXP pattern THEN
    SET temp = CONCAT(temp,ch);
   ELSE
    SET temp = CONCAT(temp,replacement);
   END IF;
   SET i=i+1;
  END LOOP;
 ELSE
  SET temp = original;
 END IF;
 RETURN temp;
END$$
DELIMITER ;

function 이름과 값 할당 순서를 잘 봐야 됩니다.
mysql8의 경우에는 regexp_replace(원본, 변경할 대상, 변경할 값) 이지만,
해당 함수의 경우에는 regexp_replace(변경할 대상, 변경할 값, 원본) 입니다.
ex) SELECT regexp_replace("[0-9]","","abcdefg123456");
결과) abcdefg

2. regexp_like
mysql8의 경우에는 regexp_like 함수가 없기 때문에 그냥 regexp를 사용해야 합니다.
ex) select * from test where column1 regexp '^[a-zA-Z]+'
결과) column1의 컬럼값이 영문자로 시작하는 대상 출력

REGEXP_INSTR, REGEXP_SUBSTR 함수도 있지만 가장 많이 사용하는 정규식 replace 구문과 정규식 검색 부분만 설명 하였습니다.

regexp 란?

like 검색과는 달리 정규식을 이용한 검색 방식을 말한다.
regexp는 우리에게 like보다 좀 더 다양한 검색을 할 수 있도록 도움을 준다.
(관련 URL : http://dev.mysql.com/doc/refman/5.1/en/regexp.html )

regexp 를 이용한 검색의 예제

select * from test where name regexp '가'
name 필드에 '가'를 포함한 모든 레코드를 출력한다.
(select * from test where name like '%가%') 의 쿼리와 동일하다

select * from test where name regexp '가|나|다|라'
name 필드에 가 또는 나 또는 다 또는 라 를 포함한 레코드를 모두 출력한다.
(select * from test where name like '%가%' or name like '%나%' or name like '%다%' or name like '%라%') 쿼리와 동일

select * from test where name regexp '[가-힇]'
name 필드에 한글이 포함된 모든 레코드를 검색한다.

select * from test where name regexp '^[가-힇]+$'
name 필드에 한글로만 구성된 모든 레코드를 검색한다.

regexp 정규식 기호에 대한 간단한 소개

. : 문자 하나를 나타낸다.
* : 앞에 나온 문자의 0개 이상 반복을 나타낸다.
^ : 문자열의 처음을 나타낸다.
$ : 문자열의 끝을 나타낸다.
[.] : 괄호 안의 문자열 일치를 확인한다.
{.} : 반복을 나타낸다.
| : or 를 나타낸다.

* 참고사항
정규식의 검색을 이용할때 절대 사용자에게 정규식 기능을 제공해선 안된다.
각 종 오류를 포함할 수 있고 sql 인젝션에 취약해지기 때문에
정규식의 검색을 개발자가 미리 정한 테두리 안에서 행해져야 한다.

MySQL 에서 정규식을 접목하여 검색하면 OR 검색조건 작성이 심플해진다.

<?php
$sql="select * from test where eng regexp '가|나|다|라'";
// 정규식을 접목한 or 검색
$s1="가";
$s2="나";
$s3="다";
$s4="라";
$sql = "select * from test where eng regexp '".$s1."|".$s2."|".$s3."|".$s4."' ";
echo $sql;
?>

select * from test where eng regexp 'do?'; // ?는 마지막 글자가 0 또는 1개 일치 : d 또는 do가 일치
select * from test where name regexp '^..$'; // 시작부터 2글자로 끝나는 이름을 찾아라.

select * from test where eng regexp '^I'; // 첫글자가 I로 시작되는 문장을 찾아라

select * from test where eng regexp '^I\''; // 첫글자가 I'로 시작되는 문장을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[0-9]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[[:digit:]]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 NOT REGEXP '^[0-9]+$'; // 숫자로만 된 것을 제외하고 찾아라

SELECT * FROM test WHERE length(col1) > 0 and col1 NOT REGEXP '^[0-9]+$'; // 숫자 제외하고 찾아라

SELECT * FROM test WHERE col1 REGEXP '[0-9]+'; // 숫자와 문자를 포함하는 것을 찾아라