MySQL & MariaDB의 세계

MySQL SUBSTRING 함수: 구문, 예제 및 모범 사례

함수
目次

1. SUBSTRING 함수란?

MySQL의 SUBSTRING 함수는 문자열의 일부분을 추출하는 데 필수적인 도구입니다. 이 함수를 사용하면 데이터베이스에서 필요한 데이터만을 가져올 수 있습니다. 예를 들어, 사용자의 이메일 주소에서 도메인을 추출하거나 제품 코드의 특정 부분을 가져오는 데 유용합니다.

1.1 기본 구문

SUBSTRING 함수의 기본 구문은 다음과 같습니다:

SUBSTRING(str, pos)
SUBSTRING(str, pos, len)
  • str : 추출 대상 문자열.
  • pos : 시작 위치(1부터 시작하는 인덱스).
  • len : 추출할 문자 수(선택 사항).

pos가 양수이면 문자열의 시작부터 계산합니다. 음수이면 문자열의 끝부터 계산합니다. len을 생략하면 지정된 위치부터 문자열 끝까지 추출합니다.

1.2 SUBSTRING 함수의 사용 사례

이 함수는 문자열 데이터를 포맷하거나 특정 부분을 추출하는 데 사용되며, 데이터베이스에서 데이터 검색 및 처리를 보다 효율적으로 만들어 줍니다.

2. SUBSTRING 함수의 기본 사용법

함수의 동작 방식을 이해하기 위해 간단한 예제를 살펴보겠습니다.

2.1 문자열의 일부 추출

다음 쿼리는 문자열 "Hello, World!"의 3번째 문자부터 6자를 추출합니다.

SELECT SUBSTRING('Hello, World!', 3, 6);

결과는 "llo, W" 입니다. pos가 3이므로 3번째 문자부터 시작하고, len을 6으로 지정했으므로 6자를 추출합니다.

2.2 길이 생략

len을 생략하면 지정된 위치부터 문자열 끝까지 추출합니다.

SELECT SUBSTRING('Hello, World!', 8);

결과는 "World!"이며, 8번째 문자부터 끝까지 추출됩니다.

2.3 음수 위치

음수 값을 사용하면 문자열 끝에서부터 위치를 지정할 수 있습니다.

SELECT SUBSTRING('Hello, World!', -5);

이 쿼리는 "orld!"를 반환하며, 마지막 5자를 추출합니다.

3. SUBSTRING 함수의 실용적인 활용

SUBSTRING 함수는 실제 데이터 작업에서 자주 사용됩니다. 몇 가지 예를 살펴보겠습니다.

3.1 이메일 주소에서 도메인 추출

SUBSTRING과 LOCATE를 결합하면 이메일 주소에서 도메인 부분을 추출할 수 있습니다.

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

이 쿼리는 "@" 기호 뒤의 문자열을 가져와 도메인 부분만 반환합니다.

3.2 제품 코드의 일부 추출

제품 코드의 특정 부분을 추출하는 예시입니다.

SELECT product_code, SUBSTRING(product_code, 5, 4) AS product_id FROM products;

이 쿼리는 제품 코드의 5번째 문자부터 4자를 추출하여 새로운 컬럼 product_id로 표시합니다.

3.3 서브쿼리와 함께 사용

서브쿼리와 결합하면 복잡한 조건에서도 데이터를 추출할 수 있습니다.

SELECT id, SUBSTRING(description, 1, 10) AS short_desc FROM (SELECT * FROM products WHERE category = 'Electronics') AS sub;

이 쿼리는 category가 ‘Electronics’인 제품들의 description에서 처음 10자를 추출합니다.

4. 다른 문자열 함수와의 비교

SUBSTRING과 유사한 목적을 가진 다른 함수로는 LEFT, RIGHT, SUBSTR가 있습니다.

4.1 LEFT와 RIGHT 함수

  • LEFT(str, len) : 문자열 시작부터 지정된 수만큼의 문자를 반환합니다.
  • RIGHT(str, len) : 문자열 끝부터 지정된 수만큼의 문자를 반환합니다.
SELECT LEFT('Hello, World!', 5);  -- "Hello"
SELECT RIGHT('Hello, World!', 6); -- "World!"

특정 끝에서 문자열의 일부분을 추출하고 싶을 때 유용합니다.

4.2 SUBSTR 함수

SUBSTR는 SUBSTRING의 별칭이며 동일하게 사용할 수 있습니다.

SELECT SUBSTR('Hello, World!', 8); -- "World!"

이 쿼리는 SUBSTRING과 마찬가지로 "World!"를 반환합니다.

5. SUBSTRING 함수의 고급 사용 및 최적화

SUBSTRING을 더 효율적으로 사용하고 성능을 최적화하는 방법입니다.

5.1 성능 최적화

대용량 데이터셋에서 SUBSTRING을 사용하면 성능에 영향을 줄 수 있습니다. 필요에 따라 인덱스를 생성하고 쿼리 실행 계획을 확인하세요. 동일한 부분 문자열을 자주 추출한다면 결과를 캐시하는 것도 도움이 됩니다.

5.2 WHERE 절에서 사용하기

WHERE 절에서 SUBSTRING을 사용하여 문자열의 일부를 기준으로 검색할 수 있습니다.

SELECT * FROM products WHERE SUBSTRING(product_code, 1, 3) = 'ABC';

이 쿼리는 product_code가 ‘ABC’로 시작하는 제품을 조회합니다.

6. SUBSTRING 함수의 예제와 모범 사례

SUBSTRING을 활용하는 실용적인 예제와 모범 사례를 소개합니다.

6.1 샘플 코드

다음 샘플은 고객의 전체 이름을 이름과 성으로 분리합니다.

SELECT name, SUBSTRING(name, 1, LOCATE(' ', name) - 1) AS first_name,
       SUBSTRING(name, LOCATE(' ', name) + 1) AS last_name
FROM customers;

이 쿼리는 공백으로 구분된 전체 이름에서 이름과 성을 추출합니다.

6.2 모범 사례

  • 최소 추출 : SUBSTRING 을 사용할 때는 필요한 부분만 추출하여 성능 영향을 최소화하세요.
  • 데이터 타입 주의 : 숫자 데이터에 SUBSTRING 을 적용할 경우, 먼저 문자열로 명시적으로 변환하세요.
  • 인덱스 고려 : WHERE 절에서 SUBSTRING 을 사용하면 인덱스가 사용되지 않을 수 있으므로, 쿼리 성능을 확인하세요.

7. 오류 처리 및 버전 차이

SUBSTRING 동작에 대한 오류 처리와 MySQL 버전별 차이를 살펴보겠습니다.

7.1 오류 처리

SUBSTRING에서 지정한 위치가 문자열 범위를 벗어나면 빈 문자열을 반환합니다. 이는 오류가 아니므로, 결과를 사전에 확인하는 로직을 추가하는 것이 권장됩니다.

7.2 버전 차이

MySQL 버전에 따라 SUBSTRING 동작이 달라질 수 있습니다. 예를 들어, 일부 오래된 버전은 멀티바이트 문자를 다르게 처리합니다. 버전 간 호환성을 확인하고 필요에 따라 적절한 조치를 취하세요.