Prevent MySQL from Casting String to Int in Queries

MySQL

On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change – that's why I'm not using it as the primary key.

In my program's code, I have a load function. First thing it does is check the richter_code field for the search parameter. If it doesn't return anything, it then goes on to search the on the ID field for the same parameter.

The problem is that it seems to truncate the value as soon as it encounters an alpha character. So I'm getting complete garbage from my query. See screenshot for example.
Can I prevent mysql from changing the query?

screenshot

Best Answer

Gotta love MySql ;)

I searched Stack Overflow for a MySql equivalent to some isnumeric function, and found this.

This rather convoluted WHERE clause would return the expected 0 rows:

where Id = case when concat('','3R100C'*1) = '3R100C' then '3R100C' else null end

The idea is to only match the parameter against Id when it is a valid numeric value. Hope it helps!

I'd create an actual isinteger function to abstract away this sheer madness:

CREATE FUNCTION `isinteger`(v varchar(255)) RETURNS bit(1)
BEGIN

    RETURN CASE WHEN CONCAT('', v * 1) = v THEN 1 ELSE 0 END;

END

That said I don't think it's right to use an auto-incremented Id field as a search-fallback column - this ID is your table's primary key, its meaning belongs strictly on the database side, no program should ever care about it, even less a program's user.

Related Question