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 중 하나와 일치

• 예시:

# 설명: `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;