sql - Like operator for integer

Question

I have a column of type bigint (ProductSerial) in my table. I need to filter the table by the Product serial using like operator. But I found that, like operator can't be used for integer type.

Is there any other method for this (I don't want to use the = operator).

Answer 1

If you must use LIKE, you can cast your number to char/varchar, and perform the LIKE on the result. This is quite inefficient, but since LIKE has a high potential of killing indexes anyway, it may work in your scenario:

... AND CAST(phone AS VARCHAR(9)) LIKE '%0203'

If you are looking to use LIKE to match the beginning or the end of the number, you could use integer division and modulus operators to extract the digits. For example, if you want all nine-digit numbers starting in 407, search for

phone / 1000000 = 407