15

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).

halfer
  • 19,824
  • 17
  • 99
  • 186
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • 6
    **Why** are you storing the phone number as an integer? – Adrian Wragg Aug 27 '13 at 10:00
  • 4
    "I have a column of type bigint (Phone number)" - well don't. use a string – Mitch Wheat Aug 27 '13 at 10:02
  • 1
    Question updated. It was product serial and not phone number. Sorry for that. – Raging Bull Aug 27 '13 at 10:17
  • 1
    @RagingBull Please don't change the question. It said Phone Numbers in your original, not Serial Numbers. – Adrian Wragg Aug 27 '13 at 10:20
  • 5
    @AdrianWragg, so users can't edit a question if they make a mistake? – bendataclear Aug 27 '13 at 10:28
  • 7
    @AdrianWragg Doesn't matter whether it is Phone number or serial number. I just wanna know that, is there any method for LIKE operation on INTEGER. thnx – Raging Bull Aug 27 '13 at 10:30
  • @bendataclear Editing is fine to clarify, but in this case he's actually changed the question being asked. – Adrian Wragg Aug 27 '13 at 10:32
  • 4
    @AdrianWragg He hasn't changed the question, he's just changed the field he's asking the questions about, same data type. It only causes problems for everyone suggesting he change the datatype assuming he can do this. – bendataclear Aug 27 '13 at 10:38
  • @bendataclear I think we'll have to agree to disagree here. Several comments and answers here were made on the basis of the original question that would not otherwise have been made; that to me is enough to indicate that the question has changed significantly enough. – Adrian Wragg Aug 27 '13 at 10:40

5 Answers5

19

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
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 7
    @MitchWheat Very often people do not have control over their schema. One shouldn't assume that they can change the structure of their table without consulting with their DBAs, architects, etc. – Sergey Kalinichenko Aug 27 '13 at 10:13
  • 3
    @MitchWheat How often do you think someone writing a query can completely re-factor the DB? I work with SQL Queries all day, if I changed any data type several applications would be FUBAR and I would be sacked. – bendataclear Aug 27 '13 at 10:27
  • 2
    "I work with SQL Queries all day," - wow, I'm impressed. I say fix the real problem, don't just accrue technical debt. – Mitch Wheat Aug 27 '13 at 10:34
  • 1
    @MitchWheat "fix the real problem", like write to the software vendor and ask them nicely to change their DB schema, then wait for this to roll out... good luck. – bendataclear Aug 27 '13 at 10:41
  • 4
    @bendataclear If a vendor was using numerics to store phone numbers, I would be re-evaluating my choice of vendor; it's indicative of ignoring best practises, and who knows what else they're doing wrong if they can't get something simple right. – Adrian Wragg Aug 27 '13 at 10:44
  • @AdrianWragg Its not about the field name. Its the datatype i am talking about. – Raging Bull Aug 27 '13 at 10:51
  • 2
    @AdrianWragg we're not dealing with a phone number now. – bendataclear Aug 27 '13 at 10:51
3

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.

Darkseal
  • 9,205
  • 8
  • 78
  • 111
1

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Albert Laure
  • 1,702
  • 5
  • 20
  • 49
  • Don't make assumptions, I've worked with a lot of telephone systems and a lot of them store phone numbers as `bigint`. – bendataclear Aug 27 '13 at 10:24
  • @bendataclear already changed the paragraph so that it is themed liked im making a Suggestion not an Assumption, thanks – Albert Laure Aug 27 '13 at 10:29
  • 1
    "I've worked with a lot of telephone systems and a lot of them store phone numbers as bigint" - DO NOT BUY SUCH systems. Written by idiots! – Mitch Wheat Aug 27 '13 at 11:54
1

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.

bendataclear
  • 3,802
  • 3
  • 32
  • 51
0

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]

Adrian Wragg
  • 7,311
  • 3
  • 26
  • 50
  • Same advice still applies to serial number. Highly unlikely they will need to do mathematical operations on it either. – Kevin Apr 18 '17 at 14:00
  • @Kevin The clarification was because the question's edits make it look like I'm answering a different question to the one asked. – Adrian Wragg May 02 '17 at 12:00