Performing a LIKE comparison on an INT field

Question

I'm fairly new to SQL and I was trying to get a full list of products that match a user input of a productID. Something like:

SELECT ProductID, ProductName FROM Products WHERE ProductID LIKE '%15%' 

I would like it to list out all the matching products such as: 15, 150, 2154, etc

Unfortunately I'm running into problems because the productID field is an INT and not a string. Is there some relatively simple way around this?

Answer 1

You can CAST the field to a string:

 ... WHERE CAST(ProductID as CHAR) LIKE '%15%' 

this is very bad for performance, as mySQL can't make use of any indexes it's created for the INT column. But then, LIKE is always slow, even when done on a varchar field: There's no way to have an index that speeds up a LIKE query.

It might be worth having a second varchar column that mirrors the int column's values and doing the LIKE on that one - you'd have to benchmark to find out whether it'll do any good.