MySQL에서 숫자가 포함된 문자열 열을 문자 기준으로 정렬하는 방법(ORDER BY)

MySQL ORDER BY로 문자열의 문자 부분 기준 정렬하기

MySQL에서 '456 John Smith'처럼 숫자와 문자가 섞여 있는 문자열이 저장된 열을 정렬할 때, 숫자가 아닌 문자(이름) 부분을 기준으로 정렬해야 하는 경우가 있습니다. 이럴 때는 ORDER BY SUBSTRING()과 LOCATE() 함수를 함께 사용하면 간단하게 해결할 수 있습니다.

예제를 통해 단계별로 살펴보겠습니다.

1. 테이블 생성

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable
(
    Id varchar(100)
);
Query OK, 0 rows affected (0.65 sec)

2. 데이터 삽입

INSERT 명령을 사용하여 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable values('456 John Smith');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('897 Adam Smith');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('1009 Bob Smith');
Query OK, 1 row affected (0.13 sec)

3. 저장된 레코드 확인

SELECT 문으로 테이블의 모든 레코드를 조회합니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+----------------+
| Id             |
+----------------+
| 456 John Smith |
| 897 Adam Smith |
| 1009 Bob Smith |
+----------------+
3 rows in set (0.00 sec)

4. 문자 기준으로 정렬하는 쿼리

이제 공백 이후의 문자(이름) 부분을 기준으로 정렬하는 쿼리입니다. LOCATE() 함수로 첫 번째 공백의 위치를 찾고, SUBSTRING() 함수로 그 위치 이후의 문자열을 추출한 뒤 이를 정렬 기준으로 사용합니다.

mysql> select *from DemoTable order by SUBSTRING(Id,LOCATE(' ', Id));

실행 결과는 다음과 같습니다.

+----------------+
| Id             |
+----------------+
| 897 Adam Smith |
| 1009 Bob Smith |
| 456 John Smith |
+----------------+
3 rows in set (0.05 sec)

결과를 보면 숫자(Id 앞부분)와 무관하게 이름인 'Adam', 'Bob', 'John'의 알파벳 순서대로 정렬된 것을 확인할 수 있습니다.

핵심 정리

• LOCATE(' ', Id): 문자열에서 첫 번째 공백의 위치를 반환합니다.
• SUBSTRING(Id, LOCATE(' ', Id)): 공백 위치부터 끝까지의 문자열(이름 부분)을 추출합니다.
• ORDER BY: 추출된 문자열을 기준으로 오름차순 정렬합니다.

이 방법은 ID와 이름이 함께 저장되어 있는 문자열 데이터를 이름 순으로 정렬해야 할 때 매우 유용하게 활용할 수 있습니다.