Like and Null in Where Clause in SQL

LIKE and NULL in WHERE clause in SQL

You can use condition like this in you where clause

where @Keyword is null or CustomerName like '%' + @Keyword + '%' 

Sql where clause, Get All if value is empty or null else use like statement

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.

Is SQL LIKE NULL valid syntax for all database?

Yes, LIKE NULL is valid in all RDBMS. LIKE is an operator followed by a string and a string can be null; so no problem.

Comparing a value to NULL, no matter what operator (<, <=, =, <>, LIKE, etc. - except for IS which is especially made to compare with NULL), results in UNKNOWN. UNKNOWN is not TRUE, so the condition is not met in case of NULL. And anyway, in case @firstName contains NULL, @firstName IS NULL evaluates to TRUE, so it doesn't even matter what LIKE @firstName results in then (because @firstName IS NULL OR firstName LIKE @firstName is TRUE when at least one of the two conditions is TRUE).

WHERE clause using values that could be NULL

Just use AND/OR logic e.g.

SELECT *
FROM Customers
WHERE ((Firstname IS NULL AND @firstname IS NULL) OR Firstname = @firstname)
AND ((Lastname IS NULL AND @lastname IS NULL) OR Lastname = @lastname);

NULL value in where clause

By default, in SQL Server, comparison operators return UNKNOWN (i.e. not true or false) if either value is a null.

MSDN IS [NOT] NULL documentation

There are ways to change how SQL Server handles null comparisons with things like SET ANSI_NULLS. Definitely pay attention to upcoming changes to the default for this value.

Here is an interesting article which covers several issues related to null handling.

SQL Server Where clause with Case Checking null value

You can use Boolean logic :

WHERE ( (@pFirstName IS NOT NULL AND first_name = @pFirstName) OR
        (@pFirstName IS NULL)
      ) AND (last_name = @pLastName);

However, the first logic you can evaluate also :

(@pFirstName IS NULL OR first_name = @pFirstName)

By using that your query would be SARGable.

like '%' does not accept NULL value

You can use coalesce to treat null like an empty string:

where COALESCE([table].[column],'') like '<parameter>'

On SQL Server, you can also use IsNull:

where IsNull([table].[column],'') like '<parameter>'

IN Clause with NULL or IS NULL

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

Why rows with NULL in column used in WHERE clause are omitted in results?

Is that bug in SQL server or I don't know something

Well, it's not a bug.

Think of NULL as a placeholder for "Unknown" and it will be clearer.

If I ask you to find me all the rows where the value is not 2 then you cannot return any NULL (unknown) value since you do not know that it is NOT 2.

If you want to include NULLs then the criteria should be

where value != 2 or value is null;

IS NULL vs = NULL in where clause + SQL Server

select column1 from Table1
  where (@param is null and column2 is null)
     or (column2 = @param)

---

Related Topics