개요
문자열, 숫자, 특수 문자가 섞여 있는 값이 저장된 열에서 특정 레코드를 조회하고 정렬해야 하는 경우가 있습니다. 이럴 때는 ORDER BY CAST()를 활용하면 됩니다. 아래 예제를 통해 구체적인 사용 방법을 살펴보겠습니다.
예제 테이블 생성하기
먼저 실습에 사용할 테이블을 생성합니다.
mysql> create table DemoTable2006 ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserCode varchar(20) ); Query OK, 0 rows affected (1.14 sec)
레코드 삽입하기
insert 명령을 사용해 테이블에 샘플 데이터를 입력합니다. UserCode 열에는 'John_12'처럼 이름과 숫자가 언더스코어(_)로 연결된 형태의 값이 저장됩니다.
mysql> insert into DemoTable2006(UserCode) values('John_12');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable2006(UserCode) values('John_34');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2006(UserCode) values('John_56');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_101');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_103');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_106');
Query OK, 1 row affected (0.07 sec)저장된 전체 레코드 확인하기
select 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable2006;
위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.
+--------+-----------+ | UserId | UserCode | +--------+-----------+ | 1 | John_12 | | 2 | John_34 | | 3 | John_56 | | 4 | Chris_101 | | 5 | Chris_103 | | 6 | Chris_106 | +--------+-----------+ 6 rows in set (0.00 sec)
특정 레코드 조회 쿼리 작성하기
이제 열에서 원하는 레코드만 추출하는 쿼리를 살펴보겠습니다. 아래 예제는 'John'으로 시작하는 코드 중 숫자 부분이 가장 큰 레코드 하나를 가져오는 쿼리입니다.
mysql> select * from DemoTable2006 where UserCode like 'John%' order by cast(substring(UserCode from 7) as signed) desc limit 1;
실행 결과는 다음과 같습니다.
+--------+----------+ | UserId | UserCode | +--------+----------+ | 3 | John_56 | +--------+----------+ 1 row in set (0.00 sec)
쿼리 동작 방식 살펴보기
위 쿼리가 의도한 대로 동작하는 이유는 각 절의 역할 덕분입니다.
WHERE 절: LIKE 'John%' 조건으로 'John'으로 시작하는 레코드만 필터링합니다.
SUBSTRING(): UserCode 값에서 7번째 문자부터 끝까지, 즉 숫자 부분('12', '34', '56')만 추출합니다.
CAST(... AS SIGNED): 추출한 문자열을 정수로 변환합니다. 이 과정이 없으면 문자열 기준 정렬로 인해 '9'가 '10'보다 크다고 판단되는 오류가 발생할 수 있습니다.
ORDER BY ... DESC와 LIMIT 1: 숫자 기준 내림차순으로 정렬한 뒤 상위 1개 레코드만 반환하므로, 결과적으로 숫자가 가장 큰 'John_56'이 조회됩니다.
마무리
이처럼 문자와 숫자가 혼합된 열에서 특정 레코드를 조회할 때는 SUBSTRING()으로 필요한 부분을 분리하고, CAST()로 숫자형으로 변환한 후 ORDER BY와 LIMIT을 조합하면 원하는 결과를 정확하게 얻을 수 있습니다.