CODEWITHSUNDEEP
sqlsql-serverwildcard
Stack user
Stack user

Reputation: 47

SQL LIKE with int

Is there anything equivalent to the LIKE we use with varchar that can be used with int?

I want to filter with three parameters, two of them are varchar so I can use LIKE and in case I get an empty string I still can retrieve all records. But how can I achieve similar thing with an int

I want to retrieve even if fkProductTypeID doesn't exist: This is my query:

select * from Product 
where Code like '%'+ @code +'%' AND  name LIKE '%'+ @name +'%'
AND fkProductTypeID = @ptype

I want it to be able to retrieve results even when I supply an ID that doesn't exist. From front-end, if I pass ' ' in @code,' ' in @name and 0 in @ptype I want it to retrieve all records

Upvotes: 0

Views: 157

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Is this what you want?

select *
from Product 
where Code like '%'+ @code +'%' AND  name LIKE '%'+ @name +'%' AND
      (fkProductTypeID = @ptype or @ptype is null);

The value '' doesn't make sense for an integer. And, don't mix types for comparisons. Just use NULL for this purpose.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460048

You could use this:

select * from Product 
where Code like '%'+ @code +'%' AND  name LIKE '%'+ @name +'%'
AND (@ptype IS NULL OR fkProductTypeID = @ptype)

if it's in a stored-procedure you should use an IF ... ELSE:

CREATE PROCEDURE dbo.SP_Name(@ptype int, @code varchar(1000), @name varchar(1000))
AS
BEGIN
    IF @ptype IS NULL
    BEGIN
        select * from Product 
        where Code like '%'+ @code +'%' AND  name LIKE '%'+ @name +'%'
    END
    ELSE
    BEGIN
        select * from Product 
        where Code like '%'+ @code +'%' AND  name LIKE '%'+ @name +'%'
        AND fkProductTypeID = @ptype
    END
END

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Its possible in this way 

 select * from Product 
 where CASE WHEN @code>0 THEN Code ELSE 0 END  like '%'+ @code +'%' 

 AND  name LIKE '%'+ @name +'%'
 AND fkProductTypeID = @ptype

Upvotes: 0

Related Questions