Click here to Skip to main content
15,846,078 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
6 Dec 2023CPOL2 min read 1.9K   1   3
SQL resolves special character challenges, streamlining NULL parameter handling
The SQL query uses the LIKE operator with the ISNULL function in a Stored Procedure to retrieve records from a table, encountering issues with special characters, offering a solution using a bit type variable to handle NULL parameters efficiently.

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.

Image 1

Running Queries

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

SQL
SELECT * FROM TEST

SELECT * FROM TEST WHERE ID = 2

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

Image 2

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:

SQL
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

SQL
EXEC GETTEST 'POST'

and a NULL

SQL
EXEC GetTest NULL

we will get this result:

Image 3

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:

SQL
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:

Image 4

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:

SQL
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

SQL
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

  • 7th December, 2023: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Engineer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionConcatenation with a NULL returns a NULL Pin
plosacco12-Dec-23 5:52
professionalplosacco12-Dec-23 5:52 
QuestionNot unexpected Pin
Dmitry Mukalov6-Dec-23 20:26
Dmitry Mukalov6-Dec-23 20:26 
AnswerRe: Not unexpected Pin
Eric Kenslow11-Dec-23 9:34
Eric Kenslow11-Dec-23 9:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.