mysql : avoid automatic cast of string in integer in where clause

Question

In my application, I use a generic where clause to search in different fields.
My request is like this :

SELECT * FROM command WHERE id = :search OR name LIKE %:search%;

I am searching the text "2SAV".
this request return some records where name contains "2SAV" (what I want) but also the record where id=2.

If I do more tests, I notice that :

SELECT CAST("2SAV" AS SIGNED); //2
SELECT 2="2SAV"; //1

If the string start with an integer, this part of string is kept.

Have you got an option or workaround to avoid this comportment?

Thanks in advance,

Antonio Attadia · Accepted Answer · 2017-10-26 15:06:08Z

1

In the MySQL documentation( https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html) is stated:

In all other cases, the arguments are compared as floating-point (real) numbers.

I think you could cast in the query:

 SELECT * FROM command WHERE id = cast(:search as char) OR name LIKE %:search%;

answered Oct 26, 2017 at 15:06

Antonio Attadia

1986 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gwen Over a year ago

Recast :search as char not work. However, I can cast the id as char char and it work. So the right request is : SELECT * FROM command WHERE CAST(id as char) = :search OR name LIKE %:search%;

Collectives™ on Stack Overflow

mysql : avoid automatic cast of string in integer in where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related