테이블의 VARCHAR 컬럼에 문자열과 숫자가 결합된 값이 저장되어 있고, 숫자 부분이 항상 오른쪽에 위치하는 경우를 생각해 봅시다. 예를 들어 다음과 같습니다.
John1023
Carol9871
David9098
이때 컬럼 전체 값 중 오른쪽에 있는 숫자를 기준으로 정렬하고 싶다면 어떻게 해야 할까요? 이런 경우 ORDER BY RIGHT() 함수를 활용하면 간단하게 해결할 수 있습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable757 (
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
ClientId varchar(100)
);
Query OK, 0 rows affected (0.53 sec)
2. 샘플 데이터 삽입하기
INSERT 명령문을 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable757(ClientId) values('John1023');
Query OK, 1 row affected (0.41 sec)
mysql> insert into DemoTable757(ClientId) values('Carol9871');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable757(ClientId) values('David9098');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable757(ClientId) values('Adam9989');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable757(ClientId) values('Bob9789');
Query OK, 1 row affected (0.20 sec)3. 전체 레코드 조회하기
SELECT 문으로 테이블의 모든 레코드를 확인해 보겠습니다.
mysql> select *from DemoTable757;
실행 결과는 다음과 같습니다.
+----+-----------+
| Id | ClientId |
+----+-----------+
| 1 | John1023 |
| 2 | Carol9871 |
| 3 | David9098 |
| 4 | Adam9989 |
| 5 | Bob9789 |
+----+-----------+
5 rows in set (0.00 sec)
4. RIGHT() 함수로 영숫자 정렬하기
MySQL에서 문자열 끝의 숫자를 기준으로 정렬하려면 아래와 같이 RIGHT() 함수를 ORDER BY 절과 함께 사용하면 됩니다.
mysql> select Id,ClientId from DemoTable757 order by right(ClientId,4);
RIGHT(ClientId, 4)는 ClientId 값의 오른쪽에서 4자리, 즉 숫자 부분만 추출하여 그 값을 기준으로 정렬합니다. 실행 결과는 다음과 같습니다.
+----+-----------+
| Id | ClientId |
+----+-----------+
| 1 | John1023 |
| 3 | David9098 |
| 5 | Bob9789 |
| 2 | Carol9871 |
| 4 | Adam9989 |
+----+-----------+
5 rows in set (0.00 sec)
결과를 보면 이름과 무관하게 오른쪽 숫자(1023 → 9098 → 9789 → 9871 → 9989)를 기준으로 오름차순 정렬된 것을 확인할 수 있습니다.
참고 사항
RIGHT() 함수는 추출할 자릿수를 고정해야 하므로, 숫자 부분의 길이가 일정하지 않은 데이터라면 결과가 왜곡될 수 있습니다. 이 경우에는 REGEXP_REPLACE()나 SUBSTRING_INDEX() 등을 조합해 숫자 부분만 분리한 뒤 CAST로 숫자형으로 변환하여 정렬하는 방법도 고려해 볼 수 있습니다.