Like operator for integer

sql sql-like
Raging Bull picture Raging Bull · Aug 27, 2013 · Viewed 45.3k times · Source

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

Sergey Kalinichenko picture Sergey Kalinichenko · Aug 27, 2013

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

Related questions

How to query MongoDB with "like"?

I want to query something with SQL's like query: SELECT * FROM users WHERE name LIKE '%m%' How to do I achieve the same in MongoDB? I can't find an operator for like in the documentation.

Read More
Combining "LIKE" and "IN" for SQL Server

Is it possible to combine LIKE and IN in a SQL Server-Query? So, that this query SELECT * FROM table WHERE column LIKE IN ('Text%', 'Link%', 'Hello%', '%World%') Finds any of these possible matches: Text, Textasd, …

Read More
SQL 'LIKE' query using '%' where the search criteria contains '%'

I have an SQL query as below. Select * from table where name like '%' + search_criteria + '%' If search_criteria = 'abc', it will return data containing xxxabcxxxx which is fine. But if my search_criteria = 'abc%', it …

Read More