MySQL에서 특정 문자 뒤의 값 추출하기
컬럼에 저장된 문자열을 특정 구분자(예: 하이픈 '-')를 기준으로 분할하고 싶다면 SUBSTRING_INDEX() 함수를 사용하면 됩니다. 이 함수는 지정한 구분자를 기준으로 문자열을 잘라내어 원하는 부분만 반환하며, 세 번째 인자에 음수를 넣으면 뒤에서부터 계산한 결과를 얻을 수 있습니다.
select substring_index(yourColumnName,'-',-1) AS anyAliasName from yourTableName;
1. 테이블 생성
먼저 예제에 사용할 테이블을 만들어 보겠습니다.
mysql> create table DemoTable
-> (
-> StreetName text
-> );
Query OK, 0 rows affected (0.60 sec)
2. 데이터 삽입
INSERT 명령을 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable values('Paris Hill St.-CA-83745646') ;
Query OK, 1 row affected (0.32 sec)
mysql> insert into DemoTable values('502 South Armstrong Street-9948443');
Query OK, 1 row affected (0.20 sec)3. 데이터 조회
SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+------------------------------------+
| StreetName |
+------------------------------------+
| Paris Hill St.-CA-83745646 |
| 502 South Armstrong Street-9948443 |
+------------------------------------+
2 rows in set (0.00 sec)
4. 특정 문자 기준으로 컬럼 분할하기
이제 하이픈(-)을 기준으로 마지막 부분(숫자 값)만 추출하는 쿼리입니다. 세 번째 인자로 -1을 전달하면 구분자가 마지막으로 나타난 위치 이후의 문자열을 반환합니다.
mysql> select substring_index(StreetName,'-',-1) AS Split from DemoTable;
실행 결과는 다음과 같습니다.
+----------+
| Split |
+----------+
| 83745646 |
| 9948443 |
+----------+
2 rows in set (0.00 sec)
SUBSTRING_INDEX() 동작 원리
SUBSTRING_INDEX(str, delim, count) 함수는 다음과 같이 동작합니다.
- count가 양수일 때: 왼쪽(앞)에서부터 count번째 구분자 앞까지의 문자열을 반환합니다.
- count가 음수일 때: 오른쪽(뒤)에서부터 count번째 구분자 뒤의 문자열을 반환합니다.
- 구분자를 찾지 못할 때: 전체 문자열을 그대로 반환합니다.
예를 들어 'Paris Hill St.-CA-83745646'이라는 값에서 substring_index(StreetName, '-', -1)은 마지막 하이픈 뒤의 '83745646'을 반환하고, 반대로 substring_index(StreetName, '-', 1)은 첫 번째 하이픈 앞의 'Paris Hill St.'를 반환합니다. 이처럼 인자 값만 조절하면 하나의 함수로 문자열의 앞부분과 뒷부분을 모두 손쉽게 추출할 수 있습니다.