[SQL] 문자열을 split한 마지막 값 가져오기

개요

SQL에서 split 함수를 이용해 구분자로 문자열을 잘랐다. 근데 프로그래밍 언어와 달리 자른 문자열 리스트에서 마지막 값을 가져오려고 -1을 사용하면 의도대로 동작하지 않았다.

 

어떤 방식으로 쿼리를 작성해야 하는지 적어둔다.

 

 

예시 테이블

예시로 어떤 파일들의 경로가 저장된 테이블이 있다고 하자. 이 데이터에서 경로를 제외한 파일의 이름만 가져오고 싶다.

| uri                                                  |
| ---------------------------------------------------- |
| Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx |
| OurStory/MeettheFoodieandtheMD.aspx                  |
| OurMenu.aspx                                         |

 

DDL

CREATE TABLE test (
  uri VARCHAR(200)
);
INSERT INTO test (uri) VALUES ('Articles/Search/ArtMID/2681/ArticleID/2218/Diet.aspx');
INSERT INTO test (uri) VALUES ('OurStory/MeettheFoodieandtheMD.aspx');
INSERT INTO test (uri) VALUES ('OurMenu.aspx');

 

 

PostgreSQL

대충 확인해 보니 PostgreSQL 13 이하의 버전에서는 SPLIT_PART 함수 결과 중 취할 position을 양수로만 지정할 수 있었다. 이 경우에는 역으로 문자열을 뒤집어 첫 번째 값을 가져오는 방식을 생각해볼 수 있다. 동작 순서는 다음과 같다.

 

1. REVERSE 함수로 문자열을 뒤집는다.

2. SPLIT_PART 함수로 문자열을 자른 후 첫번째 값을 취한다.

3. SPLIT_PART의 첫번째 값을 다시 REVERSE 함수로 뒤집는다.

SELECT REVERSE(SPLIT_PART(REVERSE(uri), '/', 1))
FROM test;

 

실행 결과

| reverse                    |
| -------------------------- |
| Diet.aspx                  |
| MeettheFoodieandtheMD.aspx |
| OurMenu.aspx               |

 

PostgreSQL 14 이상의 버전에서는 SPLIT_PART 함수의 position에 음수 지정이 가능하다. 음수를 지정한 경우 잘린 문자열의 마지막에서부터 n번째 값을 가져온다.

SELECT SPLIT_PART(uri, '/', -1)
FROM test;

 

실행 결과

| split_part                 |
| -------------------------- |
| Diet.aspx                  |
| MeettheFoodieandtheMD.aspx |
| OurMenu.aspx               |

 

 

MySQL

MySQL은 SPLIT_PART를 대신 SUBSTRING_INDEX를 지원한다. 사용 방법은 동일하다.

SELECT SUBSTRING_INDEX(uri, '/', -1) 
FROM test;

 

실행 결과

| SUBSTRING_INDEX(uri, '/', -1) |
| ----------------------------- |
| Diet.aspx                     |
| MeettheFoodieandtheMD.aspx    |
| OurMenu.aspx                  |

 

 

참고 문서

https://stackoverflow.com/questions/14412898/split-string-and-take-last-element