[MySQL] SPLIT 대신 SUBSTRING_INDEX로 문자열 잘라 UPDATE 하기

수집한 데이터를 정리하다 보면 한 칸에 들어 있는 문자열을 쪼개서 다른 칸으로 옮기고 싶을 때가 자주 있습니다. 다른 언어라면 Split() 한 번이면 끝날 일인데, MySQL에는 SPLIT 함수가 없습니다. 대신 쓰는 게 SUBSTRING_INDEX 입니다.

제가 이걸 쓴 상황은 이랬습니다. 지도 검색으로 모은 업체 목록 테이블(지도검색)에 homePage 칸이 있고, 그중 상당수가 https://blog.naver.com/아이디 형태의 네이버 블로그 주소였습니다. 블로그 아이디는 곧 네이버 메일 아이디이기도 하니, 주소 맨 끝 / 뒤의 부분만 떼어서 eMail 칸에 넣으면 됩니다.

UPDATE `지도검색` SET `eMail` = SUBSTRING_INDEX(`homePage`, '/', -1) WHERE `homePage` LIKE '%blog.naver.com%';

여기서 한 발 더 나가면 아이디 뒤에 도메인까지 붙여서 바로 쓸 수 있는 메일 주소로 만들 수 있습니다. CONCAT 으로 감싸기만 하면 됩니다.

UPDATE `지도검색` SET `eMail` = CONCAT(SUBSTRING_INDEX(`homePage`, '/', -1),'@naver.com') WHERE `homePage` LIKE '%blog.naver.com%'

SUBSTRING_INDEX는 어떻게 자르나

형식은 SUBSTRING_INDEX(문자열, 구분자, 개수) 입니다. 세 번째 인자가 핵심인데, 양수면 왼쪽부터 세어서 그 개수만큼의 구분자 앞까지, 음수면 오른쪽부터 세어서 그 개수만큼의 구분자 뒤부터 끝까지를 돌려줍니다. '하나-둘-셋' 을 - 로 잘라 보면 바로 감이 옵니다.

구분자 수보다 큰 값을 주면 원래 문자열이 통째로 나온다는 점도 표에서 보입니다. 위 UPDATE에서 -1 을 쓴 이유도 이제 설명이 되죠. 주소 맨 오른쪽의 / 뒤, 즉 마지막 조각을 가져오라는 뜻입니다.

가운데 조각이 필요할 때

이 함수는 앞에서부터 또는 뒤에서부터만 자를 수 있어서, Split(...)(1) 처럼 "두 번째 조각"을 바로 집어 오지는 못합니다. 그럴 땐 한 번 더 감쌉니다. 먼저 뒤에서 두 조각(둘-셋)을 가져오고, 그 결과에서 다시 앞의 한 조각(둘)을 가져오는 식입니다.

원래 글에 적었던 식은 다음과 같습니다.

SUBSTRING_INDEX(SUBSTRING_INDEX('하나-둘-셋' '-', -2), '-', 1)

주의: 안쪽 함수에서 '하나-둘-셋' 과 '-' 사이에 쉼표가 빠져 있습니다. 이대로 실행하면 인자 개수가 맞지 않아 오류가 납니다. 쉼표를 넣은 실제 동작하는 식은 이렇습니다.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('하나-둘-셋', '-', -2), '-', 1);
-- 안쪽 결과: '둘-셋'  →  바깥 결과: '둘'

n번째 조각을 꺼내는 일반형으로 쓰려면 안쪽을 SUBSTRING_INDEX(문자열, 구분자, n) 으로 앞의 n개를 자르고, 바깥에서 -1 로 마지막 조각을 가져오는 방식이 더 읽기 편합니다.

UPDATE 전에 꼭 확인할 것

대량 UPDATE는 되돌리기 어려우니 저는 항상 같은 식을 SELECT 로 먼저 돌려서 결과를 눈으로 확인합니다. 블로그 주소가 https://blog.naver.com/아이디/ 처럼 끝에 슬래시가 붙어 있으면 -1 로 자른 결과가 빈 문자열이 되고, PostView.nhn?blogId=... 같은 옛 주소 형식이면 엉뚱한 값이 들어갑니다. WHERE 조건에 이런 패턴을 걸러 내는 조건을 더하거나, 끝 슬래시를 TRIM(TRAILING '/' FROM homePage) 로 먼저 정리한 뒤 자르는 편이 안전합니다.

참고: daily-struggle 티스토리