MySQL - 컬럼내 문자열 자르기 SUBSTRING_INDEX

SUBSTRING_INDEX( 컬럼명, 구분 문자, 위치 )

특정 칼럼 내 문자열을 구분자로 나눠고 특정 위치의 값을 가져온다. 예를 들어 보자. 칼럼에 아래와 같은 내용이 있다. 

 

http://www.aaa.com/bbb/ccc.mp3                

 

위 주소 형식에서 앞쪽에 폴더와 도메인 주소를 다 벗겨내고 싶은 경우가 있다. "/"를 구분자로 삼아서 맨 뒤에 있는 요소를 가져오면 아마도 파일명이 나올 것이다. 아래는 SUBSTRING_INDEX를 활용한 SELECT 예제이다. 

 

SELECT 
  SUBSTRING_INDEX(audio, '/', -1) 
FROM 
  audio_info

위와 같이 하면 맨 뒤 요소인 "ccc.mp3"만 결과 값으로 돌아오게 된다. 세 번째 요소인 "위치" 항목에 음수(-)를 대입하면 뒤쪽에서 첫 번째라는 의미이다. 양수는 반대로 앞쪽부터 결과값을 돌려준다. 세 번째 요소의 수를 "3"으로 바꾸면 "/"를 기준으로 맨 처음부터 세 번째 요소까지 돌려준다. 위 예를 기준으로 하면 "http://www.aaa.com" 이 나온다. 실제 칼럼의 값을 수정하고 싶다면 아래와 같이 하면 된다. 

 

UPDATE 
  audio_table
SET 
  mp3 = (SELECT SUBSTRING_INDEX(audio, '/', -1))
WHERE
  reg_date < '2018.01.01 00:00:00'

이렇게 해주면 원래 있던 URL과 폴더를 제외한 mp3 파일명만 남게 된다. 위 명령어는 2017년 까지 등록된 모든 레코드의 mp3 파일 위치를 제거하고 파일명만 남기는 쿼리이다. MySQL 작업은 되도록 자주 안 하는 편이 정신 건강에는 좋은 듯싶다.