MySQL SUBSTRING_INDEX() 함수로 파일 확장자 추출하기

SQL 쿼리로 파일 확장자 가져오기

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

기본 문법

파일 확장자를 추출하는 기본 문법은 다음과 같습니다.

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

여기서 마지막 인자인 -1은 구분자('.')를 기준으로 마지막에 위치한 문자열, 즉 확장자를 반환하라는 의미입니다. 만약 1을 입력하면 첫 번째 부분이 반환됩니다.

예제 테이블 생성

문법을 실제로 이해하기 위해 예제 테이블을 먼저 생성해 보겠습니다.

mysql> CREATE TABLE getFileExtensionDemo
    -> (
    -> File_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> File_Name text
    -> );
Query OK, 0 rows affected (0.53 sec)

샘플 데이터 삽입

INSERT 명령어를 사용해 다양한 형식의 파일명을 포함한 레코드를 삽입합니다.

mysql> INSERT INTO getFileExtensionDemo(File_Name) VALUES('John.AllMySQLConcept.doc');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT INTO getFileExtensionDemo(File_Name) VALUES('Introductiontojava.txt');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT INTO getFileExtensionDemo(File_Name) VALUES('C and C++.AllDataStructureandAlgorithm.pdf');
Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO getFileExtensionDemo(File_Name) VALUES('C.Users.Desktop.AllMySQLScript.sql');
Query OK, 1 row affected (0.39 sec)

주목할 점은 네 번째 레코드처럼 파일명 안에 여러 개의 점(.)이 포함된 경우입니다. SUBSTRING_INDEX()의 인덱스 값이 -1이므로 마지막 점 이후의 문자열, 즉 실제 확장자가 정확히 추출됩니다.

전체 데이터 확인

SELECT 문으로 테이블의 모든 레코드를 조회합니다.

mysql> SELECT * FROM getFileExtensionDemo;

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

+---------+--------------------------------------------+
| File_Id | File_Name                                  |
+---------+--------------------------------------------+
| 1       | John.AllMySQLConcept.doc                   |
| 2       | Introductiontojava.txt                     |
| 3       | C and C++.AllDataStructureandAlgorithm.pdf |
| 4       | C.Users.Desktop.AllMySQLScript.sql         |
+---------+--------------------------------------------+
4 rows in set (0.00 sec)

파일 확장자 추출 쿼리 실행

이제 쿼리 결과로 파일 확장자만 가져오는 실제 쿼리를 실행해 보겠습니다.

mysql> SELECT SUBSTRING_INDEX(File_Name, '.', -1) AS AllFileExtension FROM getFileExtensionDemo;

실행 결과, 파일 확장자만 깔끔하게 출력됩니다.

+------------------+
| AllFileExtension |
+------------------+
| doc              |
| txt              |
| pdf              |
| sql              |
+------------------+
4 rows in set (0.20 sec)

정리

SUBSTRING_INDEX(컬럼명, '.', -1) 패턴을 사용하면 파일명에 점이 여러 개 포함되어 있더라도 항상 마지막 점을 기준으로 정확한 확장자를 추출할 수 있습니다. 대용량 데이터에서 확장자별 통계를 내거나 특정 확장자의 파일만 필터링할 때 매우 유용하게 활용할 수 있는 방법입니다.