How to get the maximum value from strings with integers in MySQL?

You can use CAST() with MAX() for this. Since the string is filled with string and integer, fir example, “STU201”, therefore we need to use CAST().

Let us first create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentBookCode varchar(200)
);
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(StudentBookCode) values('STU201');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU202');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU203');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU290');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU234');
Query OK, 1 row affected (0.15 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+-----------------+
| Id | StudentBookCode |
+----+-----------------+
| 1  | STU201          |
| 2  | STU202          |
| 3  | STU203          |
| 4  | STU290          |
| 5  | STU234          |
+----+-----------------+
5 rows in set (0.00 sec)

Following is the query to get the maximum value −

mysql> select MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) from DemoTable;

This will produce the following output −

+----------------------------------------------------------+
| MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) |
+----------------------------------------------------------+
| 290                                                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Arjun Thakur

Published on 29-Apr-2019 11:18:39