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)