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

• Related Questions & Answers
• How to get the maximum value from a column with alphanumeric strings beginning with specific characters in MYSQL?
• Get MySQL maximum value from 3 different columns?
• Get the maximum value of a column with MySQL Aggregate function
• Fetch the maximum value from a MySQL column?
• Python Pandas - Get the maximum value from Ordered CategoricalIndex
• Return maximum value from records in MySQL
• Java Program to get maximum value with Comparator
• Get maximum age from records with similar student names in MySQL
• Get the minimum value from a list with multiple columns in MySQL?
• Getting the maximum value from a varchar field in MySQL
• Two ways to fetch maximum value from a MySQL column with numbers
• How to retrieve a value with MySQL count() having maximum upvote value?
• How to get the data associated with the maximum id in a MySQL table?
• MySQL query to get substrings (only the last three characters) from strings?
• Get only the file extension from a column with file names as strings in MySQL?