CODEWITHSUNDEEP
mysqlcasting
Gwen
Gwen

Reputation: 139

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

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,

Upvotes: 0

Views: 308

Answers (1)

Antonio Attadia
Antonio Attadia

Reputation: 198

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%;

Upvotes: 1

Related Questions