왕진 블로그
MySQL SUBSTRING · SPACE · SUBSTRING_INDEX —
문자열 추출 완전 정리
문자열에서 원하는 부분만 잘라내는 작업은 실무에서 아주 자주 나온다. 이메일에서 도메인만 뽑거나, 파일 경로에서 확장자만 뽑거나, 주민번호에서 생년월일만 뽑는 식이다. 아래 세 줄의 SQL이 그 핵심 도구 3종 — SPACE, SUBSTRING, SUBSTRING_INDEX를 보여준다.
| 실행한 식 | 반환값 |
|---|---|
CONCAT('이것이', SPACE(10), 'MySQL이다') |
이것이 MySQL이다 |
SUBSTRING('대한민국만세', 3, 2) |
민국 |
SUBSTRING_INDEX('cafe.naver.com', '.', 2) |
cafe.naver |
SUBSTRING_INDEX('cafe.naver.com', '.', -2) |
naver.com |
MID라는 별칭으로도 동작한다.SUBSTRING_INDEX에서 두 번째 인자로 넘긴다. (예: '.', ',', '-')CONCAT과 함께 쓰인다.SUBSTRING의 pos나 SUBSTRING_INDEX의 count에 음수를 넣으면, 문자열의 "끝"을 기준으로 거꾸로 센다.SUBSTRING의 특수한 형태로, 각각 문자열의 맨 앞/맨 뒤에서 고정 길이만 잘라내는 단축 함수.SPACE와 자주 함께 쓰인다.SPACE(10)은 공백(스페이스) 문자 10개로 이루어진 문자열을 반환한다. 단독으로 쓰이기보다는 CONCAT과 조합해서, 두 문자열 사이에 일정한 간격을 강제로 삽입하는 용도로 쓰인다. 문자열 리터럴 안에 스페이스바를 여러 번 눌러 넣는 것보다, SPACE(n)으로 개수를 숫자로 명시하는 편이 코드 리뷰에서 "몇 칸인지"가 한눈에 드러나 가독성이 좋다.
white-space, padding) 처리가 더 안전하다. HTML은 연속 공백을 하나로 축약해버리기 때문에, SQL에서 만든 여러 칸 공백이 브라우저에서는 한 칸처럼 보일 수 있다.실무에서는 SPACE를 웹 화면보다, 고정폭 텍스트 리포트나 콘솔 출력에서 열 너비를 맞추는 용도로 더 자주 사용한다는 점도 함께 기억해두면 좋다.
SUBSTRING('대한민국만세', 3, 2)는 3번째 글자부터 2글자, 즉 '민'과 '국'을 반환해 '민국'이 나온다. 여기서 중요한 건 "3번째"가 PHP·JavaScript식으로 인덱스 2(0부터 셀 때)를 의미하는 게 아니라, 진짜 사람이 세는 방식 그대로 1번째 '대', 2번째 '한', 3번째 '민'이라는 점이다. SQL 표준 자체가 문자열·컬럼 위치를 1-based로 정의하기 때문에, MySQL의 SUBSTRING·LOCATE·INSTR 계열 함수 전부가 이 규칙을 따른다.
SUBSTRING('민국', 1, 100)은 여전히 '민국'을 반환할 뿐 예외를 던지지 않는다.길이(len) 인자를 생략하면 SUBSTRING(str, pos) 형태로 pos부터 문자열 끝까지 전부 가져온다. "몇 글자인지 모르지만 여기서부터 끝까지"가 필요할 때 매번 LENGTH()를 계산해서 len에 넣을 필요가 없다는 뜻이다. 또한 pos에 음수를 넣으면 문자열의 맨 끝을 기준으로 거꾸로 센다. SUBSTRING('대한민국만세', -2)는 끝에서 2번째 글자부터 끝까지, 즉 '만세'를 반환한다.
RIGHT(str, N) 함수로도 동일하게 처리할 수 있다. SUBSTRING(str, -N)과 RIGHT(str, N)은 대부분 같은 결과를 낸다 — 어느 쪽이 더 의도가 명확히 읽히는지로 고르면 된다.| 호출 형태 | 동작 | '대한민국만세' 결과 |
|---|---|---|
SUBSTRING(str, 3, 2) |
3번째부터 2글자 | '민국' |
SUBSTRING(str, 3) |
3번째부터 끝까지 (len 생략) | '민국만세' |
SUBSTRING(str, -2) |
끝에서 2번째부터 끝까지 | '만세' |
SUBSTRING(str, -4, 2) |
끝에서 4번째부터 2글자 | '민국' |
'cafe.naver.com'처럼 도메인 문자열은 매번 글자 수가 다르기 때문에, SUBSTRING(str, pos, len)처럼 고정된 위치·길이로는 잘라낼 수 없다. 이럴 때 쓰는 것이 SUBSTRING_INDEX다. SUBSTRING_INDEX('cafe.naver.com', '.', 2)는 '.'을 구분자로 삼아 왼쪽에서부터 세어 2번째 '.'가 나오기 "직전"까지, 즉 'cafe.naver'를 반환한다. N이 양수면 항상 왼쪽(앞)에서부터 계산한다.
N에 음수를 넣으면 기준이 오른쪽(끝)으로 뒤집힌다. SUBSTRING_INDEX('cafe.naver.com', '.', -2)는 오른쪽에서부터 2조각, 즉 'naver.com'을 반환한다. "왼쪽 몇 글자"가 아니라 "구분자 몇 개를 기준으로 어느 방향에서 자를지"가 핵심이라, 도메인의 최상위 2단계만 뽑거나(-2), 파일 경로의 마지막 폴더명만 뽑는(-1) 식으로 아주 유용하게 쓰인다.
SUBSTRING_INDEX('report.final.pdf', '.', -1) → 'pdf'처럼 N=-1을 쓴다. 반대로 확장자를 뺀 파일명만 필요하면 SUBSTRING_INDEX(str, '.', 1)처럼 N=1을 쓴다 (단, 파일명에 '.'이 여러 개면 첫 조각만 남는다는 점에 주의).세 함수는 서로 대체 가능한 상황이 많아 헷갈리기 쉽다. 기준을 "위치(숫자)로 자르는가, 구분자(패턴)로 자르는가"로 나누면 선택이 쉬워진다. 위치가 고정돼 있으면(예: 주민번호 앞 6자리 = 생년월일) SUBSTRING·LEFT·RIGHT를 쓰고, 위치가 매번 달라지고 구분자만 고정돼 있으면(예: 이메일의 '@' 뒤 도메인) SUBSTRING_INDEX를 쓴다.
예:
LEFT(주민번호, 6) → 생년월일 6자리예:
RIGHT(전화번호, 4) → 뒷자리 4자리예:
SUBSTRING_INDEX(이메일, '@', -1) → 도메인예:
SUBSTRING_INDEX(경로, '/', -1) → 파일명| 함수 | 자르는 기준 | 대표 용도 |
|---|---|---|
LEFT(str, n) |
앞에서부터 고정 길이 | 주민번호 앞 6자리, 우편번호 앞자리 |
RIGHT(str, n) |
뒤에서부터 고정 길이 | 전화번호 뒷자리, 카드번호 뒷자리 |
SUBSTRING(str, pos, len) |
임의의 시작 위치 + 길이 | 고정 서식 문자열의 중간 구간 추출 |
SUBSTRING_INDEX(str, d, n) |
구분자 기준 조각 개수 | 이메일 도메인, 파일 확장자, URL 경로 |
| 실수 | 증상 | 해결 |
|---|---|---|
| pos에 0을 넣음 | 빈 문자열이 반환돼 원인을 못 찾음 | MySQL은 1-based이므로 pos는 반드시 1부터 시작. 0은 유효한 위치가 아니다 |
| len을 음수로 넣음 | SUBSTRING(str, pos, -3)이 빈 문자열 반환 |
len(길이)은 음수를 지원하지 않는다. 방향을 바꾸려면 len이 아니라 pos에 음수를 써야 한다 |
| SUBSTRING_INDEX의 N을 문자 개수로 착각 | 기대한 글자 수만큼 안 잘리고 이상한 값이 나옴 | N은 "글자 수"가 아니라 "구분자 조각 개수" 기준이다. 구분자가 몇 번 나오는지부터 세어야 한다 |
| 구분자가 문자열에 없는 경우를 고려 안 함 | SUBSTRING_INDEX가 원본 문자열 전체를 그대로 반환 | 구분자가 없으면 자르지 않고 원본을 그대로 반환하는 게 정상 동작. NULL이 아니므로 결과 검증 로직이 필요하면 별도 분기 처리 |
| 멀티바이트(한글) 글자 수를 바이트 수로 착각 | 한글 포함 문자열에서 SUBSTRING 결과가 깨지거나 예상과 다름 | SUBSTRING은 바이트가 아니라 문자(character) 단위로 센다. 단, 컬럼의 charset이 utf8mb4 등으로 올바르게 설정돼 있어야 이 규칙이 정확히 적용된다 |
| SPACE로 만든 공백이 화면에서 사라짐 | HTML로 렌더링하면 여러 칸 공백이 한 칸으로 보임 | HTML은 연속 공백을 축약한다. 정렬 목적이면 SQL의 SPACE보다 CSS의 padding/white-space를 쓰는 게 안전하다 |
| SUBSTRING_INDEX의 N=0을 사용 | 빈 문자열이 반환돼 로직 오류처럼 보임 | N=0은 "0조각"을 의미하므로 항상 빈 문자열이 정상 결과다. 최소 단위는 1 또는 -1부터 시작한다 |
SUBSTRING, 구분자가 고정이면 SUBSTRING_INDEX, 앞뒤 글자 수가 고정이면 LEFT와 RIGHT가 더 읽기 쉽다.핵심 한 줄 요약
Tags
'php' 카테고리의 다른 글
| MySQL CONCAT_WS — 구분자 지정 문자열 연결 완전 정리 (0) | 2026.06.27 |
|---|---|
| MySQL ELT · LOCATE · INSTR — 위치·값 찾기 완전 정리 (0) | 2026.06.27 |
| MySQL PAD · TRIM — 패딩·공백 제거 완전 정리 (0) | 2026.06.27 |
| MySQL CASE WHEN — 다중 조건 분기 완전 정리 (0) | 2026.06.27 |
| MySQL IF · IFNULL · NULLIF — 조건·NULL 처리 완전 정리 (0) | 2026.06.27 |
댓글