Monday, February 06, 2012

Comparing Nullable Strings

To test if nullable @String1 is different from nullable @String2, I had this fairly complex code:

IF   (@String1 <> @String2
  
OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (
@String1 IS NOT NULL AND @String2 IS     NULL)) ...

 
Someone saw this and decided it would be simpler to do this instead:

IF (COALESCE(@String1, '') <> COALESCE(@String2, '')) ...

The problem is, that doesn't work in all cases. Here is the case where it doesn't work, followed by the cases where it does work.

First, declare and set the two string variables:

DECLARE @String1    VARCHAR(20);
DECLARE @String2    VARCHAR(20);

SET @String1 = NULL; 
SET @String2 = '';
 
Then the test:

IF (COALESCE(@String1, '') <> COALESCE(@String2, ''))
  
PRINT 'Strings Differ';


IF   (@String1 <> @String2
  
OR (@String1 IS     NULL AND @String2 IS NOT NULL)
  OR (
@String1 IS NOT NULL AND @String2 IS     NULL))
  
PRINT 'Strings Differ';


The first test does not recognize the difference, but the second test does.

All of these values pass the test:

-- 1: Both NOT NULL, different
SET @String1 = 'OldDesc';
SET @String2 = 'Desc'; 

-- 2: Both NOT NULL, same
SET @String1 = 'Desc';
SET @String2 = 'Desc';

-- 3: Both NULL
SET @String1 = NULL;
SET @String2 = NULL;

-- 4: One NULL, one NOT NULLSET @String1 = 'OldDesc'; 
SET @String2 = NULL;

5 comments:

  1. I prefer comparing like this:

    if @String1 = @String2
    print 'same'
    else
    print 'differ'

    ReplyDelete
  2. Try: ... NOT EXISTS (SELECT @String1 INTERSECT SELECT @String2)

    http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

    ReplyDelete
  3. Sirga, the one test that fails in that case is when both strings are NULL.

    DECLARE @String1 varchar(20);
    DECLARE @String2 varchar(20);

    PRINT '3: Both NULL';
    SET @String1 = NULL;
    SET @String2 = NULL;

    IF @String1 = @String2
    PRINT 'Same';
    ELSE
    PRINT 'Differ';

    Results:
    Differ

    ReplyDelete
  4. Anonymous8:19 AM

    I think a reasonable solution is to put in a value that is unlikely to be in the text you're trying to compare. Something like:

    IF (COALESCE(@String1, 'String Value Is NULL') <> COALESCE(@String2, 'String Value Is NULL'))
    PRINT 'Strings Differ'


    This seems to work well enough.

    ReplyDelete
  5. Bravo to whoever posted the anonymous comment.

    My colleague's replacement code would have worked if the replacement value in the COALESCE was not empty-string.

    This code works for all of the test cases:

    IF (
    COALESCE(@String1, 'String Value Is NULL') <>
    COALESCE(@String2, 'String Value Is NULL')
    )
    PRINT 'Differ';
    ELSE
    PRINT 'Same';

    ReplyDelete