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.

Lorem Ipsum (Test String) Generator

I am working on building test data for a large client and wanted to have strings that were not just 'x' 100 times or something like that.

I used the "Lorem Ipsum Generator" here. It allowed me to build non-repeating strings. Here are a few examples with the choices available:

Generate 25 Words
[Checked] Begin text with "Lorem ipsum dolor sit amet"
All other defaults

Lorem ipsum dolor sit amet phasellus velit placerat nam. Leo cubilia libero. Aliquam ut gravida congue non et. Lectus volutpat justo. Penatibus lectus urna wisi.

Generate 50 Words
[Unchecked] Begin text with "Lorem ipsum dolor sit amet"
All other defaults

Pharetra est scelerisque. Dolor et augue integer et non volutpat maecenas consequat id malesuada turpis. Vestibulum habitasse maecenas quis ultricies vestibulum arcu augue orci. Duis sed non vel mi lacus. Et commodo ac morbi semper commodo. Consequat pretium in. Nulla suscipit tincidunt. Diam id ut. A iaculis enim suspendisse cras.

It's a simple tool, but it creates nice strings.

What Goes Around Comes Around

My time at WebMD Health Services will come to a close at the end of this week. I am returning to Fiserv as the Data Architect for the development team in Hillsboro.

I am very excited about the new opportunity and look forward to the new challenges.