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