홈 › SQL 고급 › 09 / 10

정규식 함수

REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR

섹션 6진행 0 / 10

4. 응용 변형 예제

변형 1: REGEXP_SUBSTR 로 주문번호 뽑기

소스: sql-src/adv_09_regex/02_extract_replace.sql. 이 파일은 \1 역참조를 쓰므로 SET MODE Oracle 로 실행합니다. 메모에서 ORD-연도4자리-번호4자리 를 뽑습니다.

sql

SELECT
       id
     , memo
     , REGEXP_SUBSTR(memo, 'ORD-[0-9]{4}-[0-9]{4}') AS first_ord
  FROM contact
 ORDER BY id;

text

ID | MEMO                                      | FIRST_ORD
---+-------------------------------------------+--------------
1  | 주문 ORD-2024-0015 문의                   | ORD-2024-0015
2  | ORD-2024-0021, ORD-2024-0034 환불         | ORD-2024-0021
3  | 메모 없음                                 | NULL
4  | ord-2024-0040 소문자 표기                 | NULL
5  | 번호 오류                                 | NULL
6  | ORD-2023-0007 이전 주문                   | ORD-2023-0007
7  | ORD-2024-0051 ORD-2024-0052 ORD-2024-0053 | ORD-2024-0051
8  | NULL                                      | NULL
9  | 연락 불가                                 | NULL
(9행)

맞는 부분이 없으면 NULL 입니다. 3·4·5·9번 행이 그렇고, 4번 행은 소문자라 대소문자를 구분하는 기본 동작에서 빠졌습니다. 세 번째 인자는 시작 위치, 네 번째 인자는 몇 번째 일치인지입니다.

sql

SELECT
       id
     , REGEXP_SUBSTR(memo, 'ORD-[0-9]{4}-[0-9]{4}', 1, 2) AS second_ord
  FROM contact
 WHERE id IN (1, 2, 7)
 ORDER BY id;

결과는 2번 행 ORD-2024-0034, 7번 행 ORD-2024-0052 입니다. 1번 행은 주문번호가 하나뿐이라 두 번째가 NULL 입니다. 다섯 번째 인자에 'i' 를 주면 4번 행의 소문자 표기도 뽑히고, 여섯 번째 인자에 괄호 번호를 주면 그 묶음만 뽑습니다. 아래는 연도만 뽑는 예입니다.

sql

SELECT
       id
     , REGEXP_SUBSTR(memo, 'ORD-([0-9]{4})-[0-9]{4}', 1, 1, NULL, 1) AS ord_year
  FROM contact
 WHERE id IN (1, 6, 9)
 ORDER BY id;

결과는 1번 행 2024, 6번 행 2023, 주문번호가 없는 9번 행 NULL 입니다.

변형 2: REGEXP_REPLACE 로 정규화와 마스킹

숫자가 아닌 문자를 모두 지우는 것이 가장 흔한 치환입니다. [^0-9] 를 빈 문자열로 바꿉니다.

REGEXP_REPLACE(phone, '[^0-9]', '') 는 010 9876 5432 를 01098765432 로 만듭니다. 다음은 숫자만 남긴 뒤 괄호 묶음 세 개를 \1-\2-\3 으로 다시 이어 하이픈 형식으로 맞추는 예입니다.

sql

SELECT
       id
     , phone
     , REGEXP_REPLACE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^(01[016789])([0-9]{3,4})([0-9]{4})
  

, '\1-\2-\3') AS normalized
  FROM contact
 ORDER BY id;

text

ID | PHONE         | NORMALIZED
---+---------------+--------------
1  | 010-1234-5678 | 010-1234-5678
2  | 01012345678   | 010-1234-5678
3  | 010-123-4567  | 010-123-4567
4  | 02-345-6789   | 023456789
5  | 010-12345-678 | 010-1234-5678
6  | 010 9876 5432 | 010-9876-5432
7  | 010-5555-6666 | 010-5555-6666
8  | NULL          | NULL
9  | abc-defg-hijk | NULL
(9행)

두 가지를 봐야 합니다. 5번 행은 원래 가운데 자리가 5개인 잘못된 번호인데 정규화하자 정상처럼 보이는 값이 됐습니다. 정규화 전에 검증을 먼저 하지 않으면 오류가 감춰집니다.

또 9번 행은 숫자가 하나도 없어 결과가 빈 문자열인데 NULL 로 나왔습니다. Oracle·Tibero 는 빈 문자열을 NULL 로 다루기 때문이고(H2 도 Oracle 모드에서 같습니다), MySQL·MSSQL 은 빈 문자열로 나옵니다.

마스킹은 \1, \2 로 앞뒤를 살리고 가운데를 **** 로 바꿉니다. 패턴에 맞지 않는 행은 원래 값이 그대로 나옵니다.

sql

SELECT
       id
     , phone
     , REGEXP_REPLACE(phone, '^(01[016789])-[0-9]{3,4}-([0-9]{4})
  

, '\1-****-\2') AS masked
  FROM contact
 ORDER BY id;

형식이 맞는 1·3·7번 행만 010-****-5678 처럼 바뀝니다. 하이픈이 없거나(2번), 자리 수가 틀리거나(5번), 공백 구분(6번)인 행은 그대로입니다. 8번은 NULL 입니다.

주의
바꿀 문자열의 역참조 표기가 DB 마다 다릅니다. Oracle·Tibero 는 \1, MySQL 8.0 은 $1 을 씁니다. H2 는 기본 모드에서 $1, Oracle 모드에서 \1 이지만 MySQL 모드에서는 $1 을 풀지 않아 그대로 나옵니다(H2 에서 재현 안 됨, 문법 검토만).

변형 3: REGEXP_INSTR 와 REGEXP_COUNT

H2 2.3.232 에는 REGEXP_INSTR 와 REGEXP_COUNT 가 없어 실행하지 않습니다. 문법 검토만 하고, 아래는 예제 데이터로 손으로 그린 도식입니다.

sql

-- Oracle · Tibero (H2 미지원, 문법 검토만)
SELECT
       id
     , REGEXP_INSTR(memo, 'ORD-[0-9]{4}-[0-9]{4}') AS pos
     , REGEXP_COUNT(memo, 'ORD-[0-9]{4}-[0-9]{4}') AS cnt
  FROM contact
 WHERE id IN (1, 2, 3, 7)
 ORDER BY id;

도식(H2 미지원, 실행 결과 아님)

text

ID | POS | CNT
---+-----+----
1  | 4   | 1
2  | 1   | 2
3  | 0   | 0
7  | 1   | 3

REGEXP_INSTR 는 첫 일치의 시작 위치를 주고, 없으면 0 입니다. REGEXP_COUNT 는 Oracle 11g 부터 있고 MySQL·MSSQL 에는 없습니다. 개수가 필요하면 원래 길이에서 지운 뒤 길이를 빼는 방법을 씁니다. 아래는 숫자가 아닌 문자의 개수를 세는 예이며 H2 에서 실행됩니다. 9번 행이 NULL 인 이유는 앞의 빈 문자열 = NULL 규칙입니다.

sql

SELECT
       id
     , phone
     , LENGTH(phone) - LENGTH(REGEXP_REPLACE(phone, '[^0-9]', '')) AS not_digit_cnt
  FROM contact
 WHERE id IN (1, 6, 9)
 ORDER BY id;

결과는 1번 행 2, 6번 행 2 입니다.

변형 4: 방언별 실행

소스: sql-src/adv_09_regex/03_dialects.sql. Oracle 모드에서는 \d 약어, 두 번째 일치, \1 역참조가 모두 됩니다.

sql

-- Oracle · Tibero
SELECT
       id
     , REGEXP_LIKE(phone, '^\d{3}-\d{4}-\d{4}
  

) AS by_digit_class
     , REGEXP_SUBSTR(memo, '[0-9]+', 1, 2) AS second_number
     , REGEXP_REPLACE(phone, '^(\d{3})-(\d{4})-(\d{4})
  

, '\1-****-\3') AS masked
  FROM contact
 WHERE id IN (1, 2, 7)
 ORDER BY id;

결과는 1·7번 행이 true, 2번 행이 false(하이픈 없음), 마스킹은 010-**<strong>-5678·010-</strong>**-6666 입니다.

MySQL 은 옛 REGEXP(RLIKE) 연산자를 씁니다. RLIKE 는 H2 가 받지 않아 실행하지 않고 문법만 봅니다. 문자열 리터럴 안의 백슬래시는 MySQL 이 먼저 해석하므로 정규식의 \. 을 \\. 로 두 번 적어야 합니다.

sql

-- MySQL
SELECT
       id
     , email
  FROM contact
 WHERE email REGEXP '^[a-z]+@example\\.com
  


 ORDER BY id;

결과는 1·3·8번 행입니다.

같은 식에 NOT 을 붙인 NOT REGEXP 도 됩니다. 5·6·9번 행이 형식이 틀린 이메일로 나옵니다. 8.0 이상에서는 함수 형태를 씁니다.

sql

-- MySQL 8.0 이상
SELECT
       id
     , REGEXP_LIKE(email, '^[a-z]+@', 'i') AS by_func
     , REGEXP_SUBSTR(memo, '[0-9]{4}-[0-9]{4}') AS tail_no
     , REGEXP_REPLACE(phone, '[^0-9]', '') AS digits
  FROM contact
 WHERE id IN (1, 4, 7)
 ORDER BY id;

결과는 세 행 모두 true, 주문번호 뒷부분 2024-0015, 숫자만 남긴 전화번호입니다.

주의
MySQL 5.7 이하의 REGEXP 는 바이트 단위라 한글 같은 멀티바이트 문자에 안전하지 않고, 8.0.4 부터 ICU 로 바뀌어 동작이 다릅니다. MySQL 에는 REGEXP_COUNT 가 없습니다.

변형 5: MSSQL 은 LIKE 와 PATINDEX

MSSQL 2022 이하에는 정규식 함수가 없습니다. 대신 LIKE 의 [ ] 문자 집합과 PATINDEX 로 비슷한 검사를 합니다. H2 는 [ ] 문자 집합도 PATINDEX 도 지원하지 않아(H2 에서 재현 안 됨) 아래 두 쿼리는 문법 검토만 합니다.

sql

-- MSSQL (H2 미지원, 문법 검토만)
SELECT
       id
     , phone
  FROM contact
 WHERE phone LIKE '010-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]';

SELECT
       id
     , PATINDEX('%[0-9][0-9][0-9][0-9]%', memo) AS pos
  FROM contact;

[0-9] 는 숫자 한 글자이고 [^0-9] 는 숫자가 아닌 글자입니다. {4} 같은 반복이나 ?, + 가 없어 자리 수만큼 적어야 하고, PATINDEX 는 첫 일치 위치(없으면 0)만 줍니다. 추출과 치환은 SUBSTRING, REPLACE 와 조합합니다. 03_dialects.sql 에서는 _ 만으로 자리 수를 맞추는 대체 검사를 MSSQL 모드로 실행합니다.

sql

-- MSSQL: _ 로 자리 수만 맞추는 대체 검사
SELECT
       id
     , phone
  FROM contact
 WHERE phone LIKE '010-____-____'
    OR phone LIKE '010-___-____'
 ORDER BY id;

결과는 1·3·7번 행입니다. 숫자 여부는 이 검사로 확인하지 못하므로 실제 MSSQL 에서는 위의 [0-9] 형태를 씁니다.