문자열 추출하는 함수

MariaDB

작성일 2026-10-27 최근 수정일 2026-10-27

MariaDB에서 문자 위치로 문자열 일부를 꺼내려면 SUBSTRING(), 구분자가 나온 횟수를 기준으로 앞이나 뒤를 꺼내려면 SUBSTRING_INDEX()를 사용합니다. SUBSTRING의 위치는 보통 1부터 시작하고 음수는 뒤에서 세며, SUBSTRING_INDEX의 양수·음수 횟수는 각각 왼쪽·오른쪽에서 구분자를 세는 방향을 정합니다. 두 함수 모두 SELECT 결과를 만들 뿐 원본 열을 수정하지는 않습니다.

SUBSTRING: 시작 위치와 길이로 추출

SUBSTRING() 문서의 기본 형태는 SUBSTRING(문자열, 시작위치, 길이)입니다. 길이를 생략하면 시작 위치부터 끝까지 반환합니다. FROM·FOR로 쓰는 표준 SQL 형태와 SUBSTR라는 별칭도 지원합니다. 다음 예제는 테이블 없이 MariaDB 접속에서 실행할 수 있습니다.

SELECT SUBSTRING('12345678', 5) AS to_end,
       SUBSTRING('12345678', 5, 2) AS two_chars,
       SUBSTRING('12345678' FROM 5 FOR 2) AS standard_form,
       SUBSTR('12345678', 5, 2) AS alias_form,
       SUBSTRING(_utf8mb4'가나다라', 2, 2) AS korean;

결과는 5678, 56, 56, 56, 나다입니다. 길이 2는 끝 위치 2가 아니라 가져올 문자 수입니다. 일반 utf8mb4 문자열의 한글을 바이트 오프셋으로 자르려 하지 마세요. 바이너리 변환 여부나 눈에 한 글자처럼 보이는 조합 문자의 내부 표현에 따라 기대한 화면 글자 단위와는 다를 수 있습니다.

음수 위치·0·범위 초과·NULL

SELECT SUBSTRING('abcdef', -3) AS last_three,
       SUBSTRING('abcdef', -4, 2) AS from_back,
       SUBSTRING('abcdef', 4, 99) AS available_only,
       CONCAT('[', SUBSTRING('abc', 0), ']') AS zero_pos,
       CONCAT('[', SUBSTRING('abc', 9), ']') AS outside_pos,
       CONCAT('[', SUBSTRING('abc', 1, 0), ']') AS zero_length,
       SUBSTRING(NULL, 1, 2) AS null_input;

앞의 세 결과는 def, cd, def입니다. 음수 시작 위치는 뒤에서 세어 시작점을 정하지만, 추출 자체는 그 시작점부터 오른쪽으로 진행합니다. 길이가 남은 문자보다 길면 가능한 부분만 반환합니다. 일반 SQL 모드에서 뒤의 대괄호 결과 세 개는 모두 []이며, null_input은 NULL입니다. 빈 문자열과 NULL을 구분하기 위해 빈 결과에는 대괄호를 붙였습니다.

Oracle 호환 모드에서는 위치 0을 1로 취급하는 차이가 있습니다. 예제가 예상과 다르면 SELECT @@session.sql_mode로 현재 모드를 확인하고, 다른 애플리케이션과 공유하는 설정을 예제에 맞추려고 바꾸지는 마세요. 위치나 길이 인수도 NULL이면 결과가 NULL입니다.

SUBSTRING_INDEX: 구분자를 세어 앞·뒤 부분 선택

SUBSTRING_INDEX()는 SUBSTRING_INDEX(문자열, 구분자, 횟수) 형태입니다. 횟수가 양수이면 왼쪽에서 센 해당 구분자 앞부분, 음수이면 오른쪽에서 센 구분자 뒷부분을 돌려줍니다. 결과 내부에 있는 다른 구분자는 그대로 남습니다.

SELECT SUBSTRING_INDEX('12.34.56.78', '.', 3) AS first_three,
       SUBSTRING_INDEX('12.34.56.78', '.', -2) AS last_two,
       SUBSTRING_INDEX('alpha::beta::gamma', '::', 1) AS before_first,
       SUBSTRING_INDEX('alpha::beta::gamma', '::', -1) AS after_last;

결과는 12.34.56, 56.78, alpha, gamma입니다. 마지막 두 예처럼 구분자는 여러 문자여도 됩니다. 첫 두 값은 마침표를 가진 문자열 처리 예일 뿐 주소 형식·숫자 범위·네트워크 의미를 검증한 것이 아닙니다.

SELECT SUBSTRING_INDEX('a|b|c', '|', 9) AS too_many,
       SUBSTRING_INDEX('abc', '|', 1) AS no_delimiter,
       CONCAT('[', SUBSTRING_INDEX('a|b', '|', 0), ']') AS zero_count,
       SUBSTRING_INDEX('aXb', 'x', 1) AS case_sensitive,
       SUBSTRING_INDEX('a|b', NULL, 1) AS null_delimiter;

too_many는 a|b|c, no_delimiter는 abc입니다. 지정한 횟수만큼 구분자가 없으면 오류 대신 원본 전체가 반환될 수 있으므로, 구분자가 없을 때도 “추출 성공”으로 처리하지 않도록 주의합니다. zero_count는 [], case_sensitive는 aXb, null_delimiter는 NULL입니다. 구분자를 찾을 때는 대소문자를 구분하며 인수 중 NULL이 있으면 결과가 NULL입니다.

중첩 호출로 세 번째 항목을 고를 때의 조건

구분자로 나눈 세 번째 항목 하나가 필요하면 왼쪽 세 항목을 고른 뒤 마지막 항목을 다시 고를 수 있습니다. 다만 항목이 부족해도 마지막 기존 항목이 나올 수 있으므로 개수 조건을 함께 확인해야 합니다. 다음은 learning 실습 데이터베이스에서 CREATE·INSERT·SELECT 권한으로 실행합니다. extract_parts_demo가 이미 있으면 다른 실습 이름을 사용하세요.

USE learning;
CREATE TABLE extract_parts_demo (
    id INT PRIMARY KEY,
    parts VARCHAR(100) NULL
) DEFAULT CHARSET=utf8mb4;
INSERT INTO extract_parts_demo VALUES
    (1, 'a|b|c|d'), (2, 'a|b'), (3, 'a||'), (4, NULL);

SELECT id,
       CASE
           WHEN CHAR_LENGTH(parts) - CHAR_LENGTH(REPLACE(parts, '|', '')) >= 2
           THEN SUBSTRING_INDEX(SUBSTRING_INDEX(parts, '|', 3), '|', -1)
           ELSE NULL
       END AS third_part
FROM extract_parts_demo ORDER BY id;

id=1은 c, id=2는 NULL, id=3은 빈 문자열, id=4는 NULL입니다. 여기서는 한 문자 구분자 |가 두 번 이상 있어야 세 번째 항목이 존재한다고 판단합니다. a||는 세 번째 항목이 존재하지만 비어 있고, a|b에는 세 번째 항목 자체가 없다는 차이를 보존했습니다. 여러 문자 구분자로 바꿀 때는 길이 차이를 그대로 구분자 개수로 취급할 수 없으므로 계산 규칙도 함께 바꿔야 합니다.

이 방식은 따옴표나 이스케이프 규칙이 없는 간단한 구분 문자열에 한정됩니다. 큰따옴표 안의 쉼표를 허용하는 CSV, 구조화된 JSON, 복잡한 URL·전자우편 주소를 단순 구분자 함수만으로 완전히 파싱했다고 생각하면 안 됩니다. 저장 구조를 직접 설계할 수 있다면 반복 항목을 한 열에 이어 저장하기보다 별도 열이나 관계에 맞는 테이블로 표현하는 방식도 검토하세요.

함수 선택과 결과 확인

고정 위치·문자 수: SUBSTRING을 사용하고 위치가 1부터 시작하는지, 길이가 개수인지 확인합니다.
구분자 기준 앞·뒤: SUBSTRING_INDEX를 사용하고 방향·구분자 개수·대소문자 일치를 확인합니다.
고정된 중간 항목: 중첩 호출 전에 필요한 구분자가 실제로 존재하는지 검사합니다.
결과가 비어 보임: 빈 문자열·NULL·화면 표시를 구분하고, 원본 인수와 SQL 모드를 확인합니다.

열을 가공한 조건의 인덱스 사용 여부는 서버 버전과 조건에 따라 달라집니다. 예를 들어 공식 SUBSTRING 문서는 MariaDB 11.8부터 가능한 특정 접두부 조건의 최적화를 구분합니다. “열을 함수로 감싸면 항상 인덱스를 못 쓴다”는 일반화 대신, 실제 쿼리에 EXPLAIN을 적용하고 필요한 조회 범위와 자료형을 함께 검토하세요.

MariaDB