Search code examples
t-sqlsql-like

Any reasons why I can't replace ISNULL(col, literal) NOT LIKE literal with col <> literal if literal contains no wildcards?

I've come across, some old code, something like this example:

    DECLARE @tmpVal INT = 999

    DECLARE @tmpTable TABLE (col1 INT, col2 VARCHAR(20))

    INSERT INTO @tmpTable (col1, col2)
    VALUES 
        (1, 'Do not want this'),
        (2, 'Happy with this'),
        (3, NULL)

    SELECT
        col1,
        col2,
        -- I see code like this
        CASE
            WHEN ISNULL(col2, 'Do not want this') NOT LIKE 'Do not want this'
            THEN @tmpVal
        END                                                 AS CurrentCol,

        -- can I replace it with code like this because there is no else?
        CASE
            WHEN col2 <> 'Do not want this'
            THEN @tmpVal
        END                                                 AS BetterCol

    FROM 
        @tmpTable

My thinking is that ISNULL(col2, 'Do not want this') NOT LIKE 'Do not want this' should be replaced with col2 <> 'Do not want this' as it handles the null case perfectly.

enter image description here

Can I use the form in BetterCol over CurrentCol as the WHEN expression and if not why? Are there any edge cases I'm not seeing?

Solution

  • TL;DR; - Yes, you can replace it - it's (almost) perfectly safe.

    The longer version:

    In SQL Server, unless SET ANSI_NULL OFF is specified (and it really shouldn't be specified ever - note that it's deprecated) - any comparison of anything to a null will result with an UNKNOWN, which is basically equivalent to false for case expressions or where clause predicates.

    This means that SomeValue <> NULL and SomeValue = NULL and even NULL = NULL and NULL <> NULL will all return UNKNOWN

    Though I didn't find any documentation of the behavior of NULL and the LIKE operator (I've searched in SET ANSI_NULL, LIKE and NULL and UNKNOWN documentation) it is a well known fact that the behavior of null is the same with the like operator as it is with the = operator - meaning NULL LIKE 'some string' will return UNKNOWN as well.