MySQL에서 문자열과 숫자가 섞여 있는 데이터를 원하는 기준으로 정렬하려면 정규 표현식(REGEXP)과 CASE 문을 함께 사용하면 됩니다. 이 글에서는 알파벳 값이 먼저 오고, 그 뒤에 숫자 값이 오도록 정렬하는 방법을 예제와 함께 살펴보겠습니다.
1. 예제 테이블 생성하기
먼저 데모용 테이블을 생성합니다.
mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Value varchar(20)
);
Query OK, 0 rows affected (0.66 sec)2. 샘플 데이터 삽입하기
insert 명령을 사용해 숫자와 알파벳이 섞인 레코드를 입력합니다.
mysql> insert into DemoTable(Value) values('101');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(Value) values('P');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(Value) values('A');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Value) values('53');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(Value) values('R');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Value) values('C');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(Value) values('190');
Query OK, 1 row affected (0.12 sec)3. 전체 레코드 조회하기
select 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+----+-------+ | Id | Value | +----+-------+ | 1 | 101 | | 2 | P | | 3 | A | | 4 | 53 | | 5 | R | | 6 | C | | 7 | 190 | +----+-------+ 7 rows in set (0.00 sec)
4. ORDER BY CASE로 정렬 쿼리 작성하기
아래 쿼리는 ORDER BY 절에 CASE 문과 REGEXP를 조합한 예입니다. Value 컬럼의 값이 알파벳(a~z, A~Z)이면 0을 반환하고, 그렇지 않으면 해당 값을 숫자로 변환해 반환합니다. 이를 통해 알파벳 값이 먼저 정렬되고, 이후 숫자 값이 오름차순으로 정렬됩니다.
mysql> SELECT Id, Value FROM DemoTable ORDER BY CASE WHEN Value REGEXP '[a-zA-Z]' THEN 0 ELSE 0+Value END ASC, Value;
실행 결과는 다음과 같습니다.
+----+-------+ | Id | Value | +----+-------+ | 3 | A | | 6 | C | | 2 | P | | 5 | R | | 4 | 53 | | 1 | 101 | | 7 | 190 | +----+-------+ 7 rows in set (0.06 sec)
정렬 로직 설명
Value REGEXP '[a-zA-Z]': 값에 영문자가 포함되어 있는지 확인합니다.THEN 0: 영문자인 경우 정렬 우선순위를 0으로 지정하여 숫자보다 앞에 오게 합니다.ELSE 0+Value: 숫자인 경우 문자열을 숫자로 변환(암시적 형변환)하여 수치 기준으로 정렬합니다., Value: 첫 번째 정렬 기준이 같은 경우(영문자끼리) 사전순으로 추가 정렬합니다.
이처럼 ORDER BY 절 안에서 CASE와 REGEXP를 조합하면, 문자와 숫자가 혼재된 컬럼도 유연하게 원하는 순서대로 정렬할 수 있습니다.