SQL Server LIKE for Integer

Question

I have a column which is integer and I want to search all numbers starting with 1500.

I know I could use something like left(accountid, 4)= 1500.

But is that an optimum solution or is there a better approach? I am using SQL Server 2005.

Answer 1

LEFT(CONVERT(varchar, accountid),4)="1500"

Answer 2

If you want to go even further, why not create like a super category for accounts and that would eliminate the need to perform LEFT() on a converted varchar from an integer. To clarify, if you know all accounts beginning with id 1500 are say, accounts related to sales, you could have a super category of 1500 for all of them. This certainly only applies if this hierarchical relationship exists =).

Answer 3

Assuming you know the minimum and maximum values for your column, you can do this:

select
   stuff
from 
   table
where
   (number = 1500) 
or (number >= 15000 and number <= 15009) 
or (number >= 150000 and number <= 150099)
or (number >= 1500000 and number <= 1500999)
or (number >= 15000000 and number <= 15009999)
-- add however many statements you need.

Or if you don't know the minimums and maximums, you could do this:

...
where
    (number - 1500*POWER(10, FLOOR(LOG10(number) - 3))) 
    < (POWER(10, FLOOR(LOG10(number) - 3)))

Answer 4

It all depends on the volume of data. If you are talking about millions of records then using left with convert will not be quick. The best possible option would be have a computed column which stores the first four digits and you doing a search from it directly would be the fastest. But every insert or update would take a little more time. So it all depends on how many rows you are dealing with.

HTH

Answer 5

An INT is an INT is an INT - it's just a numeric value. An INT doesn't "look" like some string value..... if you want to compare with LIKE, you need a string - you need to convert your INT to a string to be able to do that.

If you need to search and compare against that string representation of your INT a lot, I would recommend making it a computed, persisted column on your table:

ALTER TABLE dbo.YourTable
   ADD IntString AS LEFT(CAST(YourInt AS VARCHAR(20)), 4) PERSISTED

That way, you get a new column that has a value inside it, that value is always up to date, it's a persisted column, you can index it if needed - you get all the benefits of comparing your "int" with the LIKE operator :-)

Answer 6

If you're willing to trade storage space for performance speed, I would add a persisted, computed column to the table containing the string version of the integer, index appropriately, and use that column for searching.