MySQL에서 특정 접두어를 가진 영숫자 문자열의 최대값 조회하기
영숫자가 혼합된 문자열 컬럼(예: 'IT794', 'AT1034')에서 특정 문자로 시작하는 값들 중 최대값을 구하려면 MAX() 함수와 함께 CAST()를 사용해 문자열을 숫자로 변환해야 합니다. 또한 특정 문자로 시작하는 행만 필터링하기 위해 RLIKE 연산자를 활용합니다.
먼저 예제 테이블을 생성해 보겠습니다.
mysql> create table DemoTable1381
-> (
-> DepartmentId varchar(40)
-> );
Query OK, 0 rows affected (0.48 sec)
INSERT 명령을 사용해 몇 개의 레코드를 삽입합니다.
mysql> insert into DemoTable1381 values('IT794');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1381 values('AT1034');
Query OK, 1 row affected (0.52 sec)
mysql> insert into DemoTable1381 values('IT967');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1381 values('IT874');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1381 values('AT967');
Query OK, 1 row affected (0.09 sec)SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select * from DemoTable1381;
위 쿼리는 다음과 같은 결과를 출력합니다.
+--------------+
| DepartmentId |
+--------------+
| IT794 |
| AT1034 |
| IT967 |
| IT874 |
| AT967 |
+--------------+
5 rows in set (0.00 sec)
'IT'로 시작하는 값 중 최대값 구하는 쿼리
다음은 'IT'라는 특정 문자로 시작하는 영숫자 문자열 중에서 최대값을 구하는 쿼리입니다.
mysql> select max(cast(substr(trim(DepartmentId),3) AS UNSIGNED)) from DemoTable1381 where DepartmentId RLIKE 'IT';
이 쿼리는 다음과 같은 결과를 반환합니다.
+-----------------------------------------------------+
| max(cast(substr(trim(DepartmentId),3) AS UNSIGNED)) |
+-----------------------------------------------------+
| 967 |
+-----------------------------------------------------+
1 row in set (0.10 sec)
쿼리 동작 원리
이 쿼리가 어떻게 작동하는지 단계별로 살펴보겠습니다.
1. RLIKE 'IT': WHERE 절의 RLIKE 조건은 DepartmentId 컬럼 값에 'IT'가 포함된 행만 선택합니다. 이 예제에서는 'IT794', 'IT967', 'IT874' 세 개의 행이 해당됩니다.
2. TRIM(): TRIM 함수는 문자열 앞뒤의 불필요한 공백을 제거하여 정확한 위치 계산을 보장합니다.
3. SUBSTR(..., 3): SUBSTR 함수는 세 번째 문자부터 끝까지, 즉 접두어 'IT'를 제외한 숫자 부분만 추출합니다. 예를 들어 'IT794'에서 '794'를 얻습니다.
4. CAST(... AS UNSIGNED): 추출된 문자열을 부호 없는 정수로 변환합니다. 이 과정이 중요한 이유는, 문자열 상태로 비교하면 '967'이 '1034'보다 크다고 잘못 판단될 수 있기 때문입니다. 숫자로 변환하면 올바른 수치 비교가 가능합니다.
5. MAX(): 마지막으로 변환된 숫자 값들 중 최대값을 반환합니다. 결과적으로 'IT'로 시작하는 값들 중 가장 큰 숫자인 967이 출력됩니다.
참고로, 만약 'IT'로 시작하는 값만 정확히 매칭하고 싶다면 RLIKE 대신 LIKE 'IT%'나 RLIKE '^IT'를 사용하는 것이 더 안전합니다. RLIKE 'IT'는 문자열 중간에 'IT'가 포함된 경우에도 매칭되기 때문입니다.