[Mysql v5.7] regexp_replace function 생성, 문자열 내 숫자 검출

개요

• 파일명 내에 숫자 범위 검색 가능하도록 검색 필터 기능 추가 요청받음
• Mysql v8.0 부터 regexp_replace() function 이 지원되는데, 테스트 DB 버전 확인 결과 v5.7 이었음😅
• 인터넷에 있는 소스는 동작하지 않아, 직접 mysql function 선언 복습/정리함

 

Mysql 함수 선언 형식

CREATE FUNCTION '함수명'(파라미터명 as 타입, ...)   ----- ①
RETURNS 반환할 데이터 타입  ----- ② 
BEGIN 
    선언문, 수행할 쿼리 ..  ----- ③
    RETURN 반환할 값        ----- ④
END 

 

설명

① Function Arguments/parameters
함수 매개변수/파라미터 명칭 및 타입 선언 // ex. name VARCHAR(20), age INTEGER

 

② Return Date Type
최종적으로 함수에서 RETURN 할 데이터의 타입 // ex. VARCHAR(20)

 

③ BUSINESS LOGIC

# 변수 선언
  DECLEARE 변수명 데이터타입(크기); // DEFAULT 처리 가능

# 변수 초기화
  SET 변수명 = 값;  
  SELECT 컬럼 INTO 변수명; // 함수에서 조회한 특정 컬럼 내용을 변수명에 모두 적용됨 ex. CONCAT() 처리 후 변수에 적용하는 용도

# 조건문  
  1) If Statement
     IF 조건문 THEN 실행/처리;
     ELSEIF 조건문 THEN 실행/처리;
     ELSE 실행/처리;
     END IF;

  2) Case Statement -- 아직 사용 안 해봄  
     CASE  
         WHEN 조건 THEN 처리  
         WHEN 조건 TEHN 처리  
         ELSE 처리  
     END

# 반복문
  1) Loop Statement
     [begin_label:] LOOP
         statement_list
     END LOOP [end_label]
     ------------------------
     label1: LOOP      //label1은 LOOP명칭
            SET p1 = p1 + 1;
            IF p1 < 10 THEN
                ITERATE label1;  // continue 와 같은  역할
            END IF;             // IF문 끝 부분 표시
            LEAVE label1;         // break 와 같은 역할 Loop명을 적어줘야 함.
    END LOOP label1;         // LOOP문 끝 부분 표시

   2) 그외 While Statement , Repeat ~ Until Statement 등이 있음(생략)

 

④ BUSINESS LOGIC 에서 DECLEAR(선언)한 변수 중 최종적으로 RETURN 할 변수명을 적음

 

 

참고. Mysql 5.7 공식 Reference

https://dev.mysql.com/doc/refman/5.7/en

MySQL :: MySQL 5.7 Reference Manual

 

regexp_replace() 함수 선언

- 파일명 내에 있는 숫자가 필요하므로 정규식 통해 뽑아서 형변환(CAST) 후 결과값 리턴한다

- 파일명에 숫자가 없으면 '0'을 반환

SELECT regexp_replace(대상 컬럼명|텍스트,'정규 표현식','교체 값'); 

DELIMITER $$
CREATE FUNCTION  `regexp_replace`(original VARCHAR(100), pattern VARCHAR(100), replacement VARCHAR(100))
RETURNS BIGINT      --  RETURN TYPE → BIGINT
DETERMINISTIC
BEGIN 
   DECLARE result VARCHAR(100);       -- DECLARE : 선언하다 
   DECLARE ch VARCHAR(1); 
   DECLARE i INT;
   SET i = 1;      -- 초기화
   SET result = '';
   IF original REGEXP pattern THEN          -- REGEXP 정규 표현식 만족하면 1 ( 틀리면 0 )
    loop_label: LOOP 
     IF i > CHAR_LENGTH(original) THEN
      LEAVE loop_label;  
     END IF;
     SET ch = SUBSTRING(original,i,1);       -- SUBSTRING(매개변수,시작위치(1부터),짜를 문자수)
     IF NOT ch REGEXP pattern THEN
      SET result = CONCAT(result, ch);       -- CONCAT() 연결함수
     ELSE
      SET result = CONCAT(result, replacement);
     END IF;
     SET i = i+1;
    END LOOP;
   ELSE
    SET result = original;
   END IF;
   RETURN CAST(result as unsigned);      -- 최종적으로 형변환 , 숫자가 없으면 0 
END$$
DELIMITER ;

 

 

function 생성 후 테스트 해본 결과 파일명내 번호로 범위 검색이 가능했다

SELECT *
FROM TABLE_NAME
WHERE file_name regexp '^test[0-9]+.xml$' = 1        -- test123.xml 형태의 파일명 찾음
AND regexp_replace(file_name, '[.|a-z|A-Z]', '') between 15 and 20;    -- 문자열내 숫자 검출 후 15~20 사이 값인지 filtering

 

 

 

참고. Hibernate(ORM) custom function 등록 포스팅

https://dev-ljw1126.tistory.com/124

[Hibernate] DBMS custom function 을 ORM에서 호출하는 방법