Search code examples
sqlt-sqlsql-like

SQL - Operator LIKE returns '%' or null

I needs to create a query to the database that will filter the results like this:

    [ProjectNumber] LIKE
        CASE WHEN IsNull(@ProjectId,'') = '' THEN
        '%'
        ELSE
        @ProjectId + '%'
        END
    AND
    [CountryCode] LIKE
        CASE WHEN IsNull(@Country,'') = '' THEN
        '%'
        ELSE
        @Country + '%'
        END

When both variables have value everything works, but if @Country is null then this query returns all results for which in the CountryCode column there is a value, and I need to return all (even if the field is Null). Anyone know how to write this query in case of null variable that returned all the fields (fields with value and fields with null)?

Solution

  • Instead of using CASE ... WHEN ... and ISNULL function, you can do this:

    ...
(@ProjectId IS NULL OR [ProjectNumber] LIKE @ProjectId + '%')
AND
(@Country IS NULL OR [CountryCode] LIKE @Country + '%')
...