How to Displays the maximum number in the string type column in the table using MySQL SUBSTRING-CodePudding

My column name is "momor_request" and my data is :

1000/REPORT/2022
999/REPORT/2022

If iám using SQL Query :

SELECT MAX(SUBSTRING_INDEX(nomor_request,'/',1)) AS max_number FROM my_table

It will show : 999

How to display the max value is (1000)

CodePudding user response：

Cast the numeric substring to an integer and then find the max:

SELECT MAX(CAST(SUBSTRING_INDEX(nomor_request, '/', 1) AS UNSIGNED)) AS max_number
FROM my_table;

The reason that your current query is showing 999 as being larger than 1000 is that, when sorted as text, 1000 comes before 999, as the latter starts with 1.