How to select max of mixed string/int column in MySQL?

To select max of mixed string/int column, you need to use substring() function. The syntax is as follows:

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

To understand the above syntax, let us create a table. The query to create a table is as follows:

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

Now you can insert some records in the table using insert command. The query is as follows:

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)

Display all records from the table using select statement. The query is as follows:

mysql> select *from StringIntMixHighestDemo;

The following is the output:

+-----------+---------------+ | 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)

Here is the query to get maximum value in string/int column. The query is as follows:

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

The following is the output:

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