MySQL에서 문자열과 숫자가 혼합된 varchar 데이터를 오름차순으로 정렬하는 방법
varchar 컬럼에 숫자와 문자열이 함께 저장되어 있으면, 단순히 ORDER BY만 사용할 경우 사전순(문자열 기준)으로 정렬되어 의도한 숫자 순서대로 정렬되지 않습니다. 이럴 때 ORDER BY와 IF(CAST())를 조합하면 숫자 값이 먼저 정렬되고 문자열은 뒤로 배치할 수 있습니다.
1. 테이블 생성
먼저 예제용 테이블을 생성합니다.
mysql> create table DemoTable(EmployeeCode varchar(100));
Query OK, 0 rows affected (1.17 sec)
2. 데이터 삽입
insert 명령어로 레코드를 삽입합니다.
mysql> insert into DemoTable values('190');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('100');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('120');
Query OK, 1 row affected (0.21 sec)3. 전체 데이터 조회
select 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+--------------+
| EmployeeCode |
+--------------+
| 190 |
| 100 |
| John |
| 120 |
+--------------+
4 rows in set (0.00 sec)
4. 오름차순 정렬 쿼리
문자열과 숫자 값이 모두 포함된 varchar 데이터를 오름차순으로 정렬하려면 아래 쿼리를 사용합니다.
mysql> select *from DemoTable
ORDER BY IF(CAST(EmployeeCode AS SIGNED) = 0, 100000000000, CAST(EmployeeCode AS SIGNED));
이 쿼리의 동작 원리는 다음과 같습니다. CAST(EmployeeCode AS SIGNED)로 값을 숫자로 변환한 뒤, 변환 결과가 0이면(즉, 숫자가 아닌 문자열이면) 매우 큰 값인 100000000000을 대신 부여합니다. 덕분에 숫자 데이터는 작은 값부터 먼저 정렬되고, 문자열은 가장 큰 값을 갖게 되어 자연스럽게 맨 뒤로 밀려나게 됩니다.
실행 결과를 보면 숫자들이 먼저 정렬된 것을 확인할 수 있습니다.
+--------------+
| EmployeeCode |
+--------------+
| 100 |
| 120 |
| 190 |
| John |
+--------------+
4 rows in set, 1 warning (0.00 sec)
이처럼 IF()와 CAST()를 활용하면 숫자와 문자열이 섞여 있는 varchar 컬럼도 별도의 컬럼 추가 없이 원하는 순서대로 손쉽게 정렬할 수 있습니다.