MySQL 문법 및 예제 정리 - LIKE, REGEXP, REGEXP_LIKE

MySQL에서의 LIKE, REGEXP, REGEXP_LIKE 패턴 매칭 정리

MySQL에서 문자열 패턴 매칭을 위해 LIKE와 REGEXP(정규 표현식)가 자주 사용됩니다.
이 두 가지는 특정 문자열 패턴과 일치하는 데이터를 검색할 때 유용하며, 각각의 사용법과 기능에 차이가 있습니다.
이 글에서는 LIKE와 REGEXP, REGEXP_LIKE의 사용법, 주요 기능, 그리고 차이점에 대해 설명하겠습니다.

1. LIKE 연산자

• LIKE 연산자는 문자열이 특정 패턴과 일치하는지 여부를 확인할 때 사용됩니다.
• 패턴 매칭은 주로 와일드카드(%, _)를 사용하여 이루어집니다.

• 기본 문법:

  SELECT column_name
  FROM table_name
  WHERE column_name LIKE 'pattern';
  

• 와일드카드:
• %: 0개 이상의 문자와 일치합니다.
• _: 정확히 1개의 문자와 일치합니다.

• 예시:

# 설명: `customer_name`이 `'A'`로 시작하는 모든 행을 반환합니다. `%`는 0개 이상의 문자를 대체합니다.
SELECT * FROM Customers
WHERE customer_name LIKE 'A%';

# 설명: 두 번째 문자가 `'b'`인 모든 고객을 검색합니다. `_`는 1개의 문자를 대체합니다.
SELECT * FROM Customers
WHERE customer_name LIKE '_b%';

패턴 예시 요약:

패턴	설명
'A%'	'A'로 시작하는 모든 문자열
'%B'	'B'로 끝나는 모든 문자열
'%abc%'	'abc'를 포함하는 모든 문자열
'A_C%'	`‘A’로 시작하고 3번째 문자가 ‘C’인 문자열

2. REGEXP 연산자

• REGEXP 연산자는 정규 표현식(Regular Expressions)을 사용하여 복잡한 패턴 매칭을 수행할 수 있게 해줍니다.
• LIKE보다 더 복잡한 패턴을 지원하며, 다양한 패턴 매칭 기법을 제공합니다.

• 기본 문법:

SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';

• 정규 표현식 주요 패턴:

패턴	설명
.	임의의 한 문자
^	문자열의 시작
$	문자열의 끝
[abc]	a, b, c 중 하나와 일치
[^abc]	a, b, c가 아닌 문자와 일치
[a-z]	소문자 알파벳 범위 내의 문자와 일치
[0-9]	숫자와 일치
*	0개 이상의 문자와 일치
+	1개 이상의 문자와 일치
{n}	정확히 n개의 문자와 일치
{n,}	n개 이상의 문자와 일치
{n,m}	n개 이상 m개 이하 문자와 일치
(abc\| def)	abc 또는 def 중 하나와 일치
()	패턴을 그룹화하여 하나의 단위로 처리
\d	숫자와 일치
\D	숫자가 아닌 문자와 일치
\w	단어 문자와 일치
\W	단어 문자가 아닌 문자와 일치
\s	공백 문자와 일치(스페이스, 탭, 줄바꿈)
\S	공백이 아닌 문자와 일치
\\.	.문자 자체와 일치

• 예시:

# 설명: `customer_name`이 `'A'`로 시작하는 모든 행을 반환합니다. `^`는 문자열의 시작을 의미합니다.
SELECT * FROM Customers
WHERE customer_name REGEXP '^A';

# 설명: `product_name`이 `A`, `B`, 또는 `C`로 시작하는 모든 제품을 검색합니다.
SELECT * FROM Products
WHERE product_name REGEXP '^[A-C]';

# 설명: 이메일 주소 형식(알파벳 문자로 시작하고, `@` 뒤에 `com`, `org`, `net` 도메인이 붙는 이메일)과 일치하는 행을 검색합니다.
SELECT * FROM Employees
WHERE email REGEXP '[a-z]+@[a-z]+\.(com|org|net)';

3. REGEXP_LIKE

• REGEXP_LIKE()는 MySQL 8.0부터 추가된 함수로, 정규 표현식을 기반으로 문자열이 주어진 패턴과 일치하는지 확인합니다.
• LIKE와 유사하지만, 정규 표현식을 사용하여 더 복잡한 패턴을 처리할 수 있습니다.

• 기본 문법:

SELECT column_name
FROM table_name
WHERE REGEXP_LIKE(column_name, 'pattern');

• 예시:

1. 사용자 이름이 알파벳과 숫자만 포함된 경우

# 설명: `username`이 알파벳과 숫자로만 이루어진 경우를 찾습니다.
SELECT * FROM Users WHERE REGEXP_LIKE(username, '^[a-zA-Z0-9]+$');

1. 이메일이 특정 도메인으로 끝나는 경우

# 설명: 이메일이 `gmail.com` 또는 `yahoo.com`으로 끝나는 행을 반환합니다. `\\.`는 점을 의미합니다.
SELECT * FROM Employees WHERE REGEXP_LIKE(email, '@(gmail|yahoo)\\.com$');

4. REGEXP_REPLACE

• REGEXP_REPLACE()는 문자열에서 정규 표현식과 일치하는 부분을 다른 문자열로 치환합니다.

• 기본 문법:
  • string: 검색할 문자열
  • pattern: 정규 표현식 패턴
  • replacement: 치환할 문자열

REGEXP_REPLACE(string, pattern, replacement)

• 예시:

1. 특정 패턴을 공백으로 치환하기

# 설명: 숫자가 아닌 문자를 모두 공백으로 치환하여 `1234567890`을 반환합니다.
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '') AS phone_number;

1. 이메일의 도메인 변경하기

# 설명: 이메일 주소에서 도메인 부분을 `@newdomain.com`으로 치환합니다.
SELECT REGEXP_REPLACE(email, '@.*$', '@newdomain.com') AS new_email
FROM Employees;

5. REGEXP_INSTR

• REGEXP_INSTR()는 문자열에서 정규 표현식 패턴이 처음 등장하는 위치를 반환합니다.
• 이 함수는 INSTR()와 비슷하지만 정규 표현식을 지원합니다.

• 기본 문법:
  • string: 검색할 문자열
  • pattern: 찾고자 하는 정규 표현식 패턴

REGEXP_INSTR(string, pattern)

• 예시:

1. 문자열에서 숫자가 처음 등장하는 위치 찾기

# 설명: 첫 번째 숫자가 등장하는 위치를 반환합니다. 결과는 8입니다.
SELECT REGEXP_INSTR('Contact123 for details', '[0-9]') AS first_digit_position;

1. 이메일 주소에서 도메인 부분의 시작 위치 찾기

# 설명: 이메일 주소에서 도메인이 시작되는 위치를 반환합니다.
SELECT REGEXP_INSTR(email, '@[a-zA-Z]+\\.com') AS domain_position
FROM Employees;

6. REGEXP_SUBSTR

• REGEXP_SUBSTR() 함수는 문자열에서 정규 표현식 패턴과 일치하는 부분 문자열을 반환합니다.

• 기본 문법:
  • string: 검색할 문자열
  • pattern: 찾고자 하는 정규 표현식 패턴

REGEXP_SUBSTR(string, pattern)

• 예시:

1. 문자열에서 첫 번째로 등장하는 이메일 추출

# 설명: 이메일 주소 형식과 일치하는 첫 번째 부분 문자열을 추출합니다.
SELECT REGEXP_SUBSTR('Contact us at support@example.com', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}') AS email;

1. 문자열에서 첫 번째로 등장하는 숫자 추출

# 설명: 숫자 부분을 추출하여 `12345`를 반환합니다.
SELECT REGEXP_SUBSTR('The order number is 12345', '[0-9]+') AS order_number;