MySQL에서 'John_120'과 같이 문자열과 숫자가 결합된 값을 저장한 열이 있을 때, 숫자 부분만 추출하여 정렬해야 하는 경우가 있습니다. 이럴 때 SUBSTRING_INDEX() 함수와 ORDER BY 절을 함께 사용하면 간단하게 해결할 수 있습니다.
1. 샘플 테이블 생성
먼저 실습에 사용할 테이블을 만들어 보겠습니다.
mysql> create table DemoTable1502
-> (
-> StudentId varchar(40)
-> );
Query OK, 0 rows affected (0.54 sec)
2. 데이터 삽입
INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable1502 values('John_120');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1502 values('John_201');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1502 values('Mike_178');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1502 values('Bob_198');
Query OK, 1 row affected (0.36 sec)3. 저장된 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select * from DemoTable1502;
위 쿼리는 다음과 같은 결과를 출력합니다.
+-----------+
| StudentId |
+-----------+
| John_120 |
| John_201 |
| Mike_178 |
| Bob_198 |
+-----------+
4 rows in set (0.00 sec)
4. 부분 값(숫자) 기준으로 정렬하기
SUBSTRING_INDEX() 함수는 구분자(delimiter)를 기준으로 문자열을 분할해 줍니다. 세 번째 인자가 양수면 왼쪽부터, 음수면 오른쪽부터 해당 위치까지의 문자열을 반환합니다.
- substring_index(StudentId, '_', 1) : 언더스코어(_) 앞부분, 즉 이름을 반환합니다.
- substring_index(StudentId, '_', -1) : 언더스코어(_) 뒷부분, 즉 숫자를 반환합니다.
다음은 숫자 부분(RightPart)을 기준으로 정렬하는 쿼리입니다.
mysql> select substring_index(StudentId,'_',1) as LeftPart,
-> substring_index(StudentId,'_',-1) as RightPart
-> from DemoTable1502
-> order by RightPart;
실행하면 아래와 같은 출력 결과를 얻을 수 있습니다.
+----------+-----------+
| LeftPart | RightPart |
+----------+-----------+
| John | 120 |
| Mike | 178 |
| Bob | 198 |
| John | 201 |
+----------+-----------+
4 rows in set (0.00 sec)
참고 사항
위 예제에서는 RightPart가 문자열로 반환되었지만, 다행히도 자릿수가 동일한 숫자라면 문자열 정렬과 숫자 정렬 결과가 같습니다. 하지만 'John_9'와 'John_120'처럼 자릿수가 다른 값이 섞여 있다면 문자열 비교 특성상 '9'가 '120'보다 크게 정렬됩니다. 이런 경우에는 CAST(SUBSTRING_INDEX(StudentId,'_',-1) AS UNSIGNED)처럼 숫자형으로 변환한 뒤 정렬하면 더욱 정확한 결과를 얻을 수 있습니다.