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

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