SQL 문자열 함수 — 데이터 정제의 기본

💡 지저분한 데이터를 깔끔하게 정리하는 문자열 함수를 정리한다.

---

🤔 언제 쓰나?

• 이름에서 성만 추출
• 이메일에서 도메인 추출
• 공백 제거
• 대소문자 통일
• 데이터 합치기

→ 실무 데이터는 지저분함. 정제 필수!

---

📊 예제 테이블

[users]
| user_id | name | email |
|---------|------|-------|
| 1 | 김철수 | kim@gmail.com |
| 2 | Lee Young | lee@NAVER.COM |
| 3 | 박민수 | park@kakao.com |
| 4 | 최지은 | choi@Company.co.kr |

---

1️⃣ 문자열 길이

SELECT 
    name,
    LENGTH(name) AS byte_length,      -- 바이트 수 (한글 3byte)
    CHAR_LENGTH(name) AS char_length  -- 문자 수
FROM users;

name	byte_length	char_length
김철수	9	3
Lee Young	9	9

포인트: 한글은 LENGTH와 CHAR_LENGTH 결과가 다름!

---

2️⃣ 대소문자 변환

SELECT 
    email,
    UPPER(email) AS upper_email,  -- 전부 대문자
    LOWER(email) AS lower_email   -- 전부 소문자
FROM users;

email	upper_email	lower_email
lee@NAVER.COM	LEE@NAVER.COM	lee@naver.com

활용: 대소문자 무시하고 비교

-- 대소문자 구분 없이 검색
SELECT * FROM users
WHERE LOWER(email) = LOWER('Lee@Naver.com');

---

3️⃣ 공백 제거 (TRIM)

SELECT 
    name,
    TRIM(name) AS trimmed,           -- 양쪽 공백 제거
    LTRIM(name) AS left_trimmed,     -- 왼쪽만
    RTRIM(name) AS right_trimmed     -- 오른쪽만
FROM users;

name	trimmed
' 박민수 '	'박민수'

TRIM 고급: 특정 문자 제거

-- 특정 문자 제거
SELECT TRIM(BOTH '-' FROM '--hello--');  -- 'hello'
SELECT TRIM(LEADING '0' FROM '00123');   -- '123'

---

4️⃣ 문자열 추출

SUBSTRING (부분 문자열)

SELECT 
    email,
    SUBSTRING(email, 1, 3) AS first_3,           -- 처음 3글자
    SUBSTRING(email, -3) AS last_3,              -- 마지막 3글자
    SUBSTRING(email, 1, LOCATE('@', email)-1) AS username  -- @ 앞부분
FROM users;

email	first_3	last_3	username
kim@gmail.com	kim	com	kim

LEFT / RIGHT

SELECT 
    LEFT(email, 3) AS first_3,   -- 왼쪽에서 3글자
    RIGHT(email, 3) AS last_3    -- 오른쪽에서 3글자
FROM users;

---

5️⃣ 문자열 위치 찾기

LOCATE / POSITION

SELECT 
    email,
    LOCATE('@', email) AS at_position,  -- @ 위치 (1부터 시작)
    POSITION('@' IN email) AS at_pos2   -- 같은 기능
FROM users;

email	at_position
kim@gmail.com	4

활용: 도메인 추출

SELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

email	domain
kim@gmail.com	gmail.com

---

6️⃣ 문자열 연결

CONCAT

SELECT 
    CONCAT(name, ' (', email, ')') AS full_info
FROM users;

full_info
김철수 (kim@gmail.com)

CONCAT_WS (구분자 포함)

SELECT 
    CONCAT_WS(', ', name, email, user_id) AS info
FROM users;

info
김철수, kim@gmail.com, 1

장점: NULL이 있어도 무시하고 연결!

---

7️⃣ 문자열 치환

REPLACE

SELECT 
    email,
    REPLACE(email, '@', ' [at] ') AS safe_email,
    REPLACE(name, ' ', '') AS no_space
FROM users;

email	safe_email
kim@gmail.com	kim [at] gmail.com

활용: 데이터 정제

-- 전화번호에서 하이픈 제거
SELECT REPLACE(phone, '-', '') AS clean_phone
FROM users;

-- 여러 번 치환
SELECT REPLACE(REPLACE(text, '\n', ' '), '\t', ' ') AS cleaned
FROM documents;

---

8️⃣ 문자열 분리 (SUBSTRING_INDEX)

MySQL

SELECT 
    email,
    SUBSTRING_INDEX(email, '@', 1) AS username,   -- @ 기준 첫 번째
    SUBSTRING_INDEX(email, '@', -1) AS domain     -- @ 기준 마지막
FROM users;

email	username	domain
kim@gmail.com	kim	gmail.com

복잡한 분리

-- 'a.b.c.d'에서 'b' 추출
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('a.b.c.d', '.', 2), '.', -1) AS second;
-- 결과: 'b'

---

9️⃣ 패딩 (LPAD / RPAD)

왼쪽 채우기

SELECT 
    user_id,
    LPAD(user_id, 5, '0') AS padded_id  -- 5자리, 0으로 채움
FROM users;

user_id	padded_id
1	00001
23	00023

오른쪽 채우기

SELECT RPAD(name, 10, '-') AS padded_name
FROM users;

name	padded_name
김철수	김철수-------

---

🔟 정규식 (REGEXP)

패턴 매칭

-- 숫자로 시작하는 이름
SELECT * FROM users
WHERE name REGEXP '^[0-9]';

-- 이메일 형식 검증
SELECT * FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$';

REGEXP_REPLACE (MySQL 8.0+)

-- 숫자만 제거
SELECT REGEXP_REPLACE('abc123def456', '[0-9]', '');
-- 결과: 'abcdef'

-- 연속 공백을 하나로
SELECT REGEXP_REPLACE('hello    world', '\\s+', ' ');
-- 결과: 'hello world'

---

🎯 실전 문제

문제 1: 도메인별 유저 수

이메일 도메인별 유저 수를 구하세요.

정답 보기

SELECT 
    LOWER(SUBSTRING_INDEX(email, '@', -1)) AS domain,
    COUNT(*) AS user_count
FROM users
GROUP BY LOWER(SUBSTRING_INDEX(email, '@', -1))
ORDER BY user_count DESC;

---

문제 2: 이름 정제

이름에서 앞뒤 공백 제거하고, 전부 대문자로 변환하세요.

정답 보기

SELECT 
    name AS original,
    UPPER(TRIM(name)) AS cleaned
FROM users;

---

문제 3: 마스킹 처리

이메일의 username 부분을 앞 2글자만 보이고 나머지는 *로 마스킹하세요.

정답 보기

SELECT 
    email,
    CONCAT(
        LEFT(SUBSTRING_INDEX(email, '@', 1), 2),
        REPEAT('*', CHAR_LENGTH(SUBSTRING_INDEX(email, '@', 1)) - 2),
        '@',
        SUBSTRING_INDEX(email, '@', -1)
    ) AS masked_email
FROM users;

email	masked_email
kim@gmail.com	ki*@gmail.com
park@kakao.com	pa**@kakao.com

---

💡 DB별 차이점

기능	MySQL	PostgreSQL	BigQuery
문자열 연결	CONCAT()	`
부분 문자열	SUBSTRING()	SUBSTRING()	SUBSTR()
위치 찾기	LOCATE()	POSITION()	STRPOS()
분리	SUBSTRING_INDEX()	SPLIT_PART()	SPLIT()

BigQuery 예시

-- 분리
SELECT SPLIT(email, '@')[OFFSET(0)] AS username;

-- 위치 찾기
SELECT STRPOS(email, '@') AS at_position;

---

📝 핵심 정리

[길이]
LENGTH(), CHAR_LENGTH()

[변환]
UPPER(), LOWER(), TRIM()

[추출]
SUBSTRING(str, start, length)
LEFT(str, n), RIGHT(str, n)

[위치]
LOCATE(찾을문자, 문자열)

[연결]
CONCAT(), CONCAT_WS(구분자, ...)

[치환]
REPLACE(str, from, to)

[분리]
SUBSTRING_INDEX(str, 구분자, n)

---

🔗 다음 포스트

[SQL 시리즈 #8] NULL 처리 — 실수하기 쉬운 함정들

---

📝 실무 데이터는 항상 지저분하다.
문자열 함수로 정제하는 능력이 실력이다!