I want to get the max(id) from a table colum with variable characters. it always returns 999 as the highest yet I have values like 6008 or even over 10000. I have tried the following:
SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$'
isNumeric as one of the conditions but mysql says function ..isnumeric does not exist.
How can I get the correct maxmum integer value from a column with varchar values?
Jump to PostIf your column is a varchar, it's using a string comparison. When using a string comparison "9" > "1000". You should be using integers imo.
If your column is a varchar, it's using a string comparison. When using a string comparison "9" > "1000". You should be using integers imo.
SELECT Max(CAST(column_name AS UNSIGNED)) from table_name where column_name REGEXP '^[0-9]+$'
As pritaeas said - a max on varchar would be comparing them as string values, I've had this issue before when ordering.
What you need to do is cast the field as UNSIGNED in your query and that will then return the correct value. If you are only using integers in the column, then you should definitely change the data type.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.