0

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:

  1. SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$'

  2. 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?

3
Contributors
2
Replies
14
Views
3 Years
Discussion Span
Last Post by Casperjames
1

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.

0
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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.