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;

First Simple-Talk Article

My first article at Simple-Talk is now live:What's the Point of Using VARCHAR(n) Anymore?

It is exciting to have the opportunity to write for such a well-respected magazine. It was also a very different writing experience than I'm used to. The editor is quite technical, so he provided feedback on the writing itself as well as the technical content.

I look forward to writing more for them in the future.