MySQL SUBSTRING_INDEX()로 파일명 열에서 파일 확장자만 추출하는 방법

MySQL에서 파일 이름이 저장된 문자열 열에서 파일 확장자만 추출하고 싶다면 SUBSTRING_INDEX() 함수를 사용하면 됩니다. 이 함수는 구분자를 기준으로 문자열을 나누고, 지정한 위치의 부분 문자열을 반환합니다.

SUBSTRING_INDEX() 함수 문법

SELECT SUBSTRING_INDEX(컬럼명, '.', -1) AS 별칭 FROM 테이블명;

여기서 세 번째 인자에 -1을 지정하면 마지막 마침표(.) 뒤에 있는 문자열, 즉 파일 확장자가 반환됩니다.

1. 테이블 생성하기

먼저 예제에 사용할 테이블을 생성해 보겠습니다. 아래 쿼리는 사용자 이름과 파일 이름을 저장하는 테이블을 만듭니다.

mysql> CREATE TABLE AllFiles
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserName varchar(10),
   -> FileName varchar(100)
   -> );
Query OK, 0 rows affected (0.65 sec)

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 다양한 확장자를 가진 파일 레코드를 삽입합니다.

mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Larry','AddTwoNumber.java');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Mike','AddTwoNumber.python');
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Sam','MatrixMultiplication.c');
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO AllFiles(UserName,FileName) VALUES('Carol','vector.cpp');
Query OK, 1 row affected (0.15 sec)

3. 전체 레코드 조회하기

SELECT 문으로 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> SELECT *FROM AllFiles;

실행 결과는 다음과 같습니다.

+----+----------+------------------------+
| Id | UserName | FileName               |
+----+----------+------------------------+
|  1 | Larry    | AddTwoNumber.java      |
|  2 | Mike     | AddTwoNumber.python    |
|  3 | Sam      | MatrixMultiplication.c |
|  4 | Carol    | vector.cpp             |
+----+----------+------------------------+
4 rows in set (0.00 sec)

4. 파일 확장자만 추출하기

이제 SUBSTRING_INDEX() 함수를 활용해 FileName 열에서 확장자만 추출하는 쿼리입니다.

mysql> SELECT SUBSTRING_INDEX(FileName,'.',-1) AS ALLFILENAMEEXTENSIONS FROM AllFiles;

실행 결과는 다음과 같습니다.

+-----------------------+
| ALLFILENAMEEXTENSIONS |
+-----------------------+
| java                  |
| python                |
| c                     |
| cpp                   |
+-----------------------+
4 rows in set (0.00 sec)

동작 원리 정리

SUBSTRING_INDEX(FileName, '.', -1)은 문자열을 마침표(.)를 기준으로 분리한 뒤, 음수 인덱스(-1)를 통해 오른쪽에서 첫 번째 조각을 반환합니다. 따라서 'AddTwoNumber.java'라는 파일명에서 'java'만 깔끔하게 추출할 수 있습니다. 만약 확장자 앞의 파일 이름 부분이 필요하다면 인덱스를 1로 변경하면 됩니다.