SQL Server WHERE clause

I have a SQL Server database and I want to define a query which return results b开发者_JS百科ased on only the first two numbers in a int column.

Something like:

SELECT * FROM myTable WHERE myIntColumn = 13% 

So any rows with 13, 133, 134 and 1380 would be returned

Nothing fancy, just change to varchar and pattern match.

SELECT * FROM myTable
  WHERE CAST(myIntColumn as varchar(10)) LIKE '13%'

You could integer divde by powers of 10 but it's a loop or recursion

;WITH cTE AS
(
    SELECT TOP 9
        POWER(10, ROW_NUMBER() OVER (ORDER BY column_id)) AS divisor
    FROM
        sys.columns
)
SELECT
   *
FROM
   myIntColumn
   JOIN
   cTE ON myIntColumn / divisor = 13

You need to convert the number into a string:

SELECT * FROM myTable WHERE convert(varchar, myIntColumn) like '13%'

Do take into consideration that this query will probably not benefit from an index on myIntColumn field.

You don't have to explicitly cast the column (it will be done implicitly due to the LIKE)

SELECT * FROM myTable WHERE myIntColumn like '13%'

Just for consideration, you can use maths to achieve the same result, which could be faster

SELECT * FROM myTable
WHERE myIntColumn/NULLIF(POWER(10,LEN(myIntColumn)-2),0) = 13