[데이터베이스/SQL] REGEXP 함수 정리(정규식)

이전에 MySQL 문자열 처리를 위해 정규식 함수를 정리했고
https://regexone.com/ 정규식 문제를 풀어봤다.

이제 정리한 정규식을 바탕으로 MySQL에서의 REhttps://www.hackerrank.com/domains/sql?filters%5Bsubdomains%5D%5B%5D=selectGEXP 함수들을 정리해보자.

REGEXP함수

크게 6가지로 정리할 수 있다.

0. REGEXP

SELECT *
FROM employees
WHERE first_name REGEXP '^John';

위의 쿼리문과 같이 컬럼명 REGEXP 정규식을 통해서 특정 정규식에 해당하는 결과를 출력할 수 있다. 해당 예시는 employees 테이블에서 성이 John으로 시작하는 모든 행을 출력하는 쿼리문이다.

1. REGEXP_LIKE

문자열이 정규 표현식 패턴에 일치하는지 여부를 확인하는 데 사용
REGEXP_LIKE(문자열, 정규 표현식 패턴, match_parameter, 옵션)

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE REGEXP_LIKE(Name, '^(Lucy|Ella)$')

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME LIKE 'Lucy' OR NAME LIKE 'Ella'

위의 쿼리는 두개 모두 ANIMAL_INS 테이블에서 Name 열의 값이 'Lucy' 또는 'Ella'로 시작하는 동물들을 검색하는 것이다. 즉, REGEXP_LIKE는 LIKE를 정규식으로 풀어낼 수 있다.
이때 이것은 RLIKE와 그냥 REGEXP로도 처리할 수 있다.

• 다양한 옵션들
  c: 대소문자를 구분하여 비교
  i: 대소문자를 구분하지 않고 비교
  n: . 메타 문자가 개행 문자(\n)와 일치하지 않도록
  m: 여러 행 모드로 처리하여 ^와 $는 각 행의 시작과 끝을 의미

SELECT
  'hello, World!' AS original_text,
  REGEXP_LIKE('hello, World!', '^[A-Z]', 'i') AS check_upper;

이 쿼리는 대문자로 시작하는지 확인하는 것으로 이때 i 옵션은 대소문자를 구분하지 않기때문에 소문자로 시작하지만 True로 1을 반환한다. 그러나 이때 c 옵션을 택한다면 소문자로 시작하며 대소문자를 구분하기 때문에 False, 0을 반환한다.

2. REGEXP_REPLACE

이 함수는 REPLACE함수를 정규식으로 풀어내는 것이다.
REGEXP_REPLACE(문자열, 정규 표현식 패턴(대체될 문자열), 대체할 문자(열))

-- 일반적인 REPLACE()함수
SELECT
  'Hello, World!' AS original_text,
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE('Hello, World!', 'a', '*'), 'e', '*'), 'i', '*'), 'o', '*'), 'u', '*'
      ) AS replaced_text
FROM example_table;;


-- REGEXP_REPLACE()함수
SELECT
	'Hello, World!' AS original_text,
	REGEXP_REPLACE('Hello, World!', '[aeiou]', '*') AS replaced_text
FROM example_table;

이 쿼리들는 문자열 "Hello, World!"에서 소문자 모음을 찾아 '*'로 대체하는 것이다.
즉, 여러문자를 동시에 대체해야한다면 후자가 더 효율적이다.

3. REGEXP_INSTR

이 함수는 문자열에서 정규 표현식 패턴에 처음으로 일치하는 부분의 시작 위치를 반환하는 함수. 만약 일치하는 부분이 없다면 0을 반환하고 문자열이 NULL이라면 NULL을 반환.
REGEXP_INSTR(문자열, 정규 표현식 패턴, [시작 인덱스])

SELECT
	'Hello, World!' AS original_text,
    REGEXP_INSTR('Hello, World!', '[aeiou]') AS match_position
FROM example_table;;

처음으로 일치하는 것은 e이므로 H에 이어서 두번째로 나온다. 이때 SUBSTRING()과 같이 index가 1부터 시작한다. 따라서 2가 반환된다.

SELECT
	'Hello, World!' AS original_text,
    REGEXP_INSTR('Hello, World!', '[o]', 6) AS match_position
FROM example_table;

이렇게 되면 시작 인덱스를 추가로 인자로 제공하여 인덱스 6부터 조사하기 때문에 5번째 인덱스의 5는 탐색 대상이 아니다. 따라서 해당 문자열에서 9번째 인덱스의 o가 확인되므로 9가 반환 값이다.

4. REGEXP_SUBSTR

문자열에서 정규 표현식 패턴에 일치하는 부분을 추출하는 함수
REGEXP_SUBSTR(문자열, 정규 표현식 패턴, [시작위치], [일치하는 부분 중에서 몇 번째 일치하는 것인지])

SELECT
    'The cat and the hat' AS original_text,
    REGEXP_SUBSTR('The cat and the hat', 'the', 1, 1, 'i') AS matched_substring
FROM example_table;

The를 출력

5. REGEXP_COUNT

문자열에서 일치하는 패턴의 수를 세는 데 사용

SELECT
    'The cat and the hat' AS original_text,
    REGEXP_COUNT('The cat and the hat', '(?i)the') AS match_count
FROM example_table;

2를 출력

프로그래머스 환경에서 이것 저것 돌려본 결과

• \d이거 안먹혀서 숫자로가려면 [0-9]이걸로 가야함
• REGEXP_COUNT()이 작동하지 않음
• REGEXP_INSTR()은 잘됨

그냥 해본거

select product_code, 
CASE
    WHEN LENGTH(REGEXP_SUBSTR(product_code,  '[0-9]+')) = 7 THEN concat(product_code, '0')
end as "hi"
from product;

-- 중간중간에 숫자가 껴있는 id에서 숫자 갯수 알아낼때
LENGTH(PRODUCT_CODE) - LENGTH(REGEXP_REPLACE(PRODUCT_CODE, '[A-Z]', ''))
-- 전체 길이에서 문자열을 ''로 대체해 제거해서 길이를 세면 숫자만 나올것이다.
-- 해당 쿼리를 case에서 when에서 조건으로 검사해서 추가적인 처리를 해주면될것같다.