MySQL에서 문자열과 숫자가 혼합된 열의 최대값 구하는 방법

MySQL에서 'INV129'처럼 문자열과 숫자가 섞여 있는 열에서 최대값을 구하려면 SUBSTRING() 함수와 CAST() 함수를 함께 사용해야 합니다. 문자열 부분을 제거하고 숫자만 추출한 뒤, 이를 정수형으로 변환하여 MAX()로 비교하는 원리입니다.

기본 문법

SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, LENGTH(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;

위 문법에서 SUBSTRING의 두 번째 인자인 4는 접두사(예: 'INV')가 3글자이므로 숫자가 시작되는 위치를 의미하고, 세 번째 인자는 전체 길이에서 접두사 길이 3을 뺀 값입니다.

예제 테이블 생성

먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table StringIntMixHighestDemo
    -> (
    -> InvoiceId int NOT NULL AUTO_INCREMENT,
    -> InvoiceNumber varchar(20),
    -> PRIMARY KEY(InvoiceId)
    -> );
Query OK, 0 rows affected (0.65 sec)

데이터 삽입

INSERT 명령을 사용해 샘플 데이터를 입력합니다.

mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV122');
Query OK, 1 row affected (0.22 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV1');
Query OK, 1 row affected (0.15 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV145');
Query OK, 1 row affected (0.18 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV19');
Query OK, 1 row affected (0.10 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV134');
Query OK, 1 row affected (0.13 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV135');
Query OK, 1 row affected (0.16 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV100');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV121');
Query OK, 1 row affected (0.27 sec)

저장된 데이터 확인

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

mysql> select *from StringIntMixHighestDemo;

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

+-----------+---------------+
| InvoiceId | InvoiceNumber |
+-----------+---------------+
|         1 | INV129        |
|         2 | INV122        |
|         3 | INV1          |
|         4 | INV145        |
|         5 | INV19         |
|         6 | INV134        |
|         7 | INV135        |
|         8 | INV10         |
|         9 | INV100        |
|        10 | INV121        |
+-----------+---------------+
10 rows in set (0.00 sec)

혼합 열에서 최대값 구하기

이제 문자열/숫자 혼합 열에서 최대값을 추출하는 쿼리를 실행합니다.

mysql> SELECT MAX(CAST(SUBSTRING(InvoiceNumber, 4, length(InvoiceNumber)-3) AS UNSIGNED)) as HighestValue
   -> from StringIntMixHighestDemo;

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

+--------------+
| HighestValue |
+--------------+
|          145 |
+--------------+
1 row in set (0.00 sec)

결과를 보면 숫자 기준으로 가장 큰 값인 145(INV145)가 정상적으로 반환된 것을 확인할 수 있습니다. 만약 단순히 MAX(InvoiceNumber)를 실행하면 문자열 비교가 이루어져 잘못된 결과가 나올 수 있으므로, 반드시 위와 같이 숫자 부분만 분리한 후 CAST로 변환해야 정확한 최대값을 얻을 수 있습니다.