LIKE with integers, in SQL

Question

Can I replace the = statement with the LIKE one for the integers ?

by eg. are the following the same thing:

select * from FOOS where FOOID like 2    
// and 
select * from FOOS where FOOID = 2

I'd prefer to use LIKE instead of = because I could use % when I have no filter for FOOID...

SQL Server 2005.

EDIT 1 @Martin

Answer 1

select * from FOOS where FOOID like 2

should be avoided as it will cause both sides to be implicitly cast as varchar and mean that an index cannot be used to satisfy the query.

CREATE  TABLE #FOOS
(
FOOID INT PRIMARY KEY,
Filler CHAR(1000)
)
INSERT INTO #FOOS(FOOID)
SELECT DISTINCT number 
FROM master..spt_values


SELECT * FROM #FOOS WHERE FOOID LIKE 2

SELECT * FROM #FOOS WHERE FOOID = 2

DROP TABLE #FOOS

Plans (notice the estimated costs)

Another way of seeing the difference in costs is to add SET STATISTICS IO ON

You see that the first version returns something like

Table '#FOOS__000000000015'. Scan count 1, logical reads 310

The second version returns

Table '#FOOS__000000000015'. Scan count 0, logical reads 2

This is beacuse the reads required for the seek on this index are proportional to the index depth whereas the reads required for the scan are proportional to the number of pages in the index. The bigger the table gets the larger the discrepancy between these 2 numbers will become. You can see both of these figures by running the following.

SELECT index_depth, page_count
FROM
sys.dm_db_index_physical_stats (2,object_id('tempdb..#FOOS'), DEFAULT,DEFAULT, DEFAULT)
WHERE object_id = object_id('tempdb..#FOOS') /*In case it hasn't been created yet*/

Answer 2

Use a CASE statement to convert an input string to an integer. Convert the wildcard % to a NULL. This will give better performance than implicitly converting the entire int column to string.

CREATE PROCEDURE GetFoos(@fooIdOrWildcard varchar(100))
AS
BEGIN
    DECLARE @fooId int

    SET @fooId =
        CASE
            -- Case 1 - Wildcard 
            WHEN @fooIdOrWildcard = '%'
                THEN NULL
            -- Case 2 - Integer
            WHEN LEN(@fooIdOrWildcard) BETWEEN 1 AND 9
            AND @fooIdOrWildcard NOT LIKE '%[^0-9]%'
                THEN CAST(@fooIdOrWildcard AS int)
            -- Case 3 - Invalid input
            ELSE 0
        END

    SELECT FooId, Name
    FROM dbo.Foos
    WHERE FooId BETWEEN COALESCE(@fooId, 1) AND COALESCE(@fooId, 2147483647)
END

Answer 3

Yes, you can just use it:

SELECT  *
FROM    FOOS
WHERE   FOOID like 2   

or

SELECT  *
FROM    FOOS
WHERE   FOOID like '%'  

Integers will be implicitly converted into strings.

Note that neither of these condition is sargable, i. e. able to use an index on fooid. This will always result in a full table scan (or a full index scan on fooid).

Answer 4

This is a late comment but I thought maybe some other people are looking for the same thing so as I was able to find a solution for this, I thought I should share it here:)

A short description of the problem: the problem I had was to be able to use the wild card foe integer data types. I am using SQL Server and so my syntax is for SQL Server. I have a column which shows department number and I wanted to pass a variable from my page from a drop down menu. There is an 'All' option as well which in that case I wanted to pass '%' as the parameter. I was using this:

select * from table1 where deptNo Like @DepartmentID

It was working for when I pass a number but not for % because sql server implicitly converts the @DepartmentID to int (as my deptNo is of type int)

So I casted the deptNo and that fixed the issue:

select * from table1 where CAST(deptNo AS varchar(2)) Like @DepartmentID

This one works for both when I pass a number like 4 and when I pass %.

Answer 5

Use NULL as the parameter value instead of % for your wildcard condition

select * from table1 where (@DepartmentID IS NULL OR deptNo = @DepartmentID)