이해를 돕기 위해 'customerdetail'이라는 테이블에 저장된 다음 데이터를 사용하겠습니다.
mysql> Select * from Customerdetail; +----------------------+----------------------+----------+---------------------+ | Name | FName | Address | Emailid | +----------------------+----------------------+----------+---------------------+ | Advik Jhamb | Lovkesh Jhamb | Mumbai | Advik@gmail.com | | Chirag Jai Patil | Raman Jai Patil | Gujrat | chirahp@yahoo.com | | Devansh Singh Rajput | Kishore Singh Rajput | Rajastan | Devansh@Hotmail.com | | Mitul Kumar Sharma | Om Veer Sharma | Patiala | Mitul@gmail.com | +----------------------+----------------------+----------+---------------------+ 4 rows in set (0.00 sec)
이제 Name 컬럼의 전체 이름을 'First_Name(이름)', 'Middle_Name(중간 이름)', 'Last_Name(성)' 세 부분으로 분할하고 싶다면 아래 쿼리를 사용하면 됩니다.
mysql> SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1) AS First_Name, If( length(Name) - length(replace(Name, ' ', ''))>1, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1), NULL) AS Middle_Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 3), ' ', -1) AS Last_Name, Address FROM customerdetail; +----------------------+------------+-------------+-----------+----------+ | Name | First_Name | Middle_Name | Last_Name | Address | +----------------------+------------+-------------+-----------+----------+ | Advik Jhamb | Advik | NULL | Jhamb | Mumbai | | Chirag Jai Patil | Chirag | Jai | Patil | Gujrat | | Devansh Singh Rajput | Devansh | Singh | Rajput | Rajastan | | Mitul Kumar Sharma | Mitul | Kumar | Sharma | Patiala | +----------------------+------------+-------------+-----------+----------+ 4 rows in set (0.00 sec)
결과 집합에서 확인할 수 있듯이 각 이름이 성공적으로 세 부분으로 나뉘었습니다. 특히 'Advik Jhamb'처럼 중간 이름이 없는 경우에는 Middle_Name이 자동으로 NULL로 처리되는 점이 눈에 띕니다.
SUBSTRING_INDEX() 함수의 동작 원리
SUBSTRING_INDEX(str, delim, count) 함수는 지정한 구분자(delim)가 count번째로 나타나는 위치를 기준으로 문자열을 잘라 반환합니다. count가 양수이면 왼쪽에서부터, 음수이면 오른쪽에서부터 계산한다는 점이 핵심입니다.
쿼리 단계별 분석
- First_Name 추출: SUBSTRING_INDEX(Name, ' ', 1)은 첫 번째 공백 앞의 문자열, 즉 첫 번째 단어를 그대로 반환합니다.
- Middle_Name 추출: 먼저 length(Name) - length(replace(Name, ' ', ''))로 공백의 개수를 계산합니다. 공백이 2개 이상일 때만 IF 조건문이 참이 되어 두 번째 단어를 추출하고, 공백이 1개뿐이면 NULL을 반환합니다.
- Last_Name 추출: SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 3), ' ', -1)은 앞의 세 단어를 먼저 가져온 뒤, 다시 오른쪽에서 첫 번째 단어를 꺼내 마지막 단어를 얻습니다. 단어가 두 개뿐인 이름이라도 마지막 단어가 정상적으로 반환됩니다.
이처럼 SUBSTRING_INDEX() 함수를 중첩해서 사용하면 별도의 저장 프로시저나 애플리케이션 로직 없이도 순수 SQL만으로 이름을 손쉽게 분리할 수 있습니다.