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

Answer 2

Although I'm a bit late to the party, I'd like to add the method I'm using to match the first N given numbers (in the example, 123) in any numeric-type column:

SELECT * FROM MyTable WHERE MyColumn / POWER(10, LEN(MyColumn) - LEN(123)) = 123

The technique is similar to @dasblinkenlight's one, but it works regardless of the number of digits of the target column values. This is a viable workaround if your column contain numbers with different length and you don't want to use the CAST+LIKE method (or a calculated column).

For additional details on that (and other LIKE workarounds) check out this blog post that I wrote on this topic.

Answer 3

You can change your Field PhoneNumbers and store as String and then use the Like You can alter your table so that you can use the LIKE statement, if you still want to use BIGint for your phone numbers, you cannot get the exact Phone Number without using = the method you can use is Between method that looks for the Numbers that are inside the range.

For the edited question: I think you should use = sign for their ID, or convert the Int to String and then Use Like.

Answer 4

If you have control over the database you could add a calculated column to copy the integer value to a string:

ALTER TABLE MyTable
ADD CalcCol AS (CAST(ProductSerial AS VARCHAR)) PERSISTED

And query like:

SELECT *
FROM MyTable
WHERE ProductSerial LIKE '%2548%'

This will move the calculation to the insert/update and only on rows inserted/updated rather then converting every row for each query. This may be a problem if there are a lot of updated to columns as it will add a very small overhead to these.

There may be a way to do it mathematically using modulus but this would take a lot of working out and testing.

Answer 5

The original question related to a phone number. OP has since edited it to refer to serial numbers. This answer refers to the original question only.

My suggestion is to avoid storing your phone numbers as integers in the first place, and thus the problem does not occur. My phone number is in the form, internationally, of:

    +44 7844 51515

Storing it as an integer makes no sense here, as you will never need to do any mathematical operation on it, and you would lose the leading plus. Within the UK, it is:

    07844 51515

and thus storing it as an integer would lose its leading zero. Unless you have a very very specific requirement to store it as an integer, you would fare significantly better storing it as a string instead.

[Note: Not actually my phone number]