SQL Server: Unexpected Behavior of the LIKE Operator When Used with the ISNULL Function

Introduction

When using the LIKE operator together with the ISNULL function to select records from a table, we might not get the expected results if the field that we are using for the search contains some special characters.

Preparation

To better understand this, let’s set the stage for our example.

1. Create a simple table:
   SQL

   CREATE TABLE [dbo].[Test](
   [Id] [int] NULL,
   [Post] [nvarchar](max) NULL
   )

2. Add few records:
   SQL

   INSERT INTO TEST (ID, POST) VALUES(1, 'Michelangelo di Lodovico Buonarroti Simoni, _
   known mononymously as Michelangelo,
   was an Italian sculptor, painter, architect, and poet of the High Renaissance.')
   INSERT INTO TEST (ID, POST) VALUES(2, 'The closest stars cover ¼N1δ2 of the sky')
   INSERT INTO TEST (ID, POST) VALUES(3, 'Post by far [at list at this moment] _
   better than all the others')
   INSERT INTO TEST (ID, POST) VALUES(4, 'Normal Post')
   INSERT INTO TEST (ID, POST) VALUES(5, 'Other Post')
   INSERT INTO TEST (ID, POST) VALUES_
   (6, 'Leonardo di ser Piero da Vinci was an Italian polymath of the High Renaissance
   who was active as a painter, draughtsman, engineer, scientist, theorist, sculptor, _
   and architect.')

Very importantly, note that the insert #3 has square brackets in it.

Running Queries

Running “normal” queries will result in the expected outcome:

SELECT * FROM TEST

SELECT * FROM TEST WHERE ID = 2

SELECT * FROM TEST WHERE POST LIKE '%Post%'

But let’s say that we need to build a Stored Procedure to find the records in our table whose Post field contains a given string. Our Stored Procedure will have a parameter, and this parameter could also be NULL. In this last case, the Stored Procedure will return all the records. The code of the procedure is the following:

CREATE PROCEDURE GetTest(@Search NVARCHAR(500))
AS

SELECT * FROM TEST
WHERE Post LIKE ('%' + ISNULL(@Search, Post) + '%')

Executing the Stored Procedure using a string as parameter

EXEC GETTEST 'POST'

and a NULL

EXEC GetTest NULL

we will get this result:

As you can see, the second call does not return the expected results, because the record #3 is missing. I can’t claim to be a SQL SERVER expert but it seems to me that when the parameter @Search is NULL, the SQL engine compares the value of the field with itself BUT the special characters “[“ and “]“ are not handled the way one would expect. That's probably because they are part of a set of characters used for special purposes by the SQL Engine.

The Least Painful Solution

One way out of this problem is to change the Stored Procedure like this:

ALTER PROCEDURE GetTest(@Search NVARCHAR(500))
AS

IF(@Search IS NULL)
      SELECT * FROM TEST
ELSE
      SELECT * FROM TEST
      WHERE Post like ('%' + @Search + '%')

Now it will return the right output:

But real world SELECT are much more complex then the one shown above and very likely, the number of search parameter that can be NULL is bigger than one. The result of using the above approach with a complex SELECT? A lot of IF branches and a very messy code.

A much better approach is to create a bit type variable and set it to true if the value of the parameter is NULL. Then we use that variable in the SELECT statement:

ALTER PROCEDURE GetTest(@Search NVARCHAR(500))
AS

DECLARE @SearchParameterIsNull bit = 0
IF (@Search IS null) SET @SearchParameterIsNull = 1

SELECT * FROM TEST
      WHERE
      (@SearchParameterIsNull = 1 or Post like ('%' + ISNULL(@Search, Post) + '%'))

We can now get the expected results without using IF statements.

Points of Interest

The solution requires the code

DECLARE @SearchParameterIsNull bit = 0
IF (@Search IS null) SET @SearchParameterIsNull = 1

to be repeated for every parameter of the Stored Procedure that can be null. It is not ideal and I would really appreciate any suggestion to improve the solution.

Happy coding!

History

• 7^th December, 2023: Initial version