문자열에 포함된 숫자 값으로 정렬하기
MySQL에서 'CSE 101', 'CSE 11'처럼 문자와 숫자가 결합된 문자열 데이터를 다루다 보면, 문자열 전체가 아닌 숫자 부분만을 기준으로 정렬해야 하는 경우가 자주 발생합니다. 단순히 ORDER BY를 사용하면 사전순으로 정렬되어 'CSE 101'이 'CSE 11'보다 앞에 오는 문제가 생깁니다. 이 글에서는 SUBSTRING, LOCATE, CAST 함수를 조합하여 이 문제를 해결하는 방법을 소개합니다.
1. 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable1969
(
BranchCode varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
2. 샘플 데이터 삽입
INSERT 문을 사용하여 테이블에 레코드를 추가합니다.
mysql> insert into DemoTable1969 values('CSE 101');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1969 values('CSE 11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1969 values('CSE 15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1969 values('CSE 6');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1969 values('CSE 201');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1969 values('CSE 110');
Query OK, 1 row affected (0.00 sec)3. 저장된 레코드 확인
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1969;
실행 결과는 다음과 같습니다.
+------------+
| BranchCode |
+------------+
| CSE 101 |
| CSE 11 |
| CSE 15 |
| CSE 6 |
| CSE 201 |
| CSE 110 |
+------------+
6 rows in set (0.00 sec)
4. 숫자 값 기준 정렬 쿼리
이제 문자열에서 공백 뒤의 숫자 부분만 추출한 뒤, 이를 정수형으로 변환하여 내림차순 정렬하는 쿼리를 살펴보겠습니다.
mysql> select * from DemoTable1969
order by CAST(SUBSTRING(BranchCode,LOCATE(' ',BranchCode)+1) AS SIGNED) DESC;
각 함수의 역할은 다음과 같습니다.
- LOCATE(' ', BranchCode): 문자열에서 공백의 위치를 찾습니다.
- SUBSTRING(...): 공백 다음 위치부터 끝까지, 즉 숫자 부분만 추출합니다.
- CAST(... AS SIGNED): 추출된 문자열을 정수로 변환하여 올바른 숫자 비교가 가능하게 합니다.
위 쿼리를 실행하면 다음과 같이 숫자 크기 기준으로 정확하게 정렬된 결과를 확인할 수 있습니다.
+------------+
| BranchCode |
+------------+
| CSE 201 |
| CSE 110 |
| CSE 101 |
| CSE 15 |
| CSE 11 |
| CSE 6 |
+------------+
6 rows in set (0.00 sec)
마무리
이처럼 LOCATE와 SUBSTRING으로 숫자 부분을 분리하고 CAST로 형변환하면, 문자와 숫자가 섞인 문자열 데이터도 원하는 숫자 순서대로 정렬할 수 있습니다. 오름차순으로 정렬하려면 DESC 대신 ASC를 사용하면 됩니다.