MySQL 숫자만 영문만 한글만 추출

아래처럼 MySQL 문자열 칼럼에서 특정 타입(숫자, 영문, 한글 등)만 추출하는 방법을 정리한다.

 

원래 문자열	추출 문자열
주문번호 20210708000032 결제	20210708000032

 

세 가지 방법 중 선택

• CAST 함수 사용 : 숫자로 시작하는 문자열만 가능, 앞부분만 가능
• REGEXP_REPLACE 함수 사용 : MySQL 버전 8부터 사용 가능
• 사용자 함수 생성

사용자 함수 생성

8 버전 이하 환경에서 REGEXP_REPLACE 함수와 똑같이 동작하는 함수를 만들기 위해, 아래처럼 regex_replace 함수를 생성.

DELIMITER $$

CREATE FUNCTION `regex_replace`(
  original VARCHAR(1000) CHARACTER SET utf8,
  pattern VARCHAR(1000) CHARACTER SET utf8,
  replacement VARCHAR(1000) CHARACTER SET utf8
) RETURNS VARCHAR(1000) CHARSET utf8
  DETERMINISTIC
BEGIN
  DECLARE temp VARCHAR(1000) CHARACTER SET utf8;
  DECLARE ch VARCHAR(1) CHARACTER SET utf8;
  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 ;

함수 이름을 REGEXP_REPLACE라고 똑같이 생성하면, 상위 버전의 MySQL 서버로 DB를 이전하는 경우 문제가 발생할 수 있으므로 다르게 지정.

테스트해 보면 잘 동작한다.

SELECT regex_replace('abc(가나다?)123', '[가-힣]', '') -- 한글 : abc(?)123
SELECT regex_replace('abc(가나다?)123', '[0-9]', '') -- 숫자 : abc(가나다?)
SELECT regex_replace('abc(가나다?)123', '[a-z]', '') -- 영문 : (가나다?)123
SELECT regex_replace('abc(가나다?)123', '[^[:alnum:][:space:]]+', '') -- 특수문자 : abc가나다123
SELECT regex_replace('abc(가나다?)123', '[`~!#$%^&*|\\\'\";:\/?]', '') -- 특수문자 특정 : abc(가나다)123

SELECT regex_replace('abc(가나다?)123', '[가-힣0-9a-z]', '') -- 한글, 숫자, 영문 제거 : (?)

사용자 함수 생성(숫자만 추출)

물론 위에서 생성한 함수에 정규식을 조합하여 사용할 수 있지만, 참고 삼아 숫자만 추출하는 함수도 기재한다.

DELIMITER $$

CREATE FUNCTION `digits`(str CHAR(32)) RETURNS CHAR(32) CHARSET utf8
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);

  IF str IS NULL
  THEN 
    RETURN "";
  END IF;

  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END$$

DELIMITER ;

 

 

참고문헌 및 관련링크.

1. MySQL 8.0 - 추가된 String functions
2. MYSQL utf8 procedure나 function에서 한글 깨짐문제