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, differentSET @String1 = 'OldDesc';SET @String2 = 'Desc'; -- 2: Both NOT NULL, sameSET @String1 = 'Desc';SET @String2 = 'Desc';-- 3: Both NULLSET @String1 = NULL;SET @String2 = NULL;-- 4: One NULL, one NOT NULLSET @String1 = 'OldDesc'; SET @String2 = NULL;
I prefer comparing like this:
ReplyDeleteif @String1 = @String2
print 'same'
else
print 'differ'
Try: ... NOT EXISTS (SELECT @String1 INTERSECT SELECT @String2)
ReplyDeletehttp://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
Sirga, the one test that fails in that case is when both strings are NULL.
ReplyDeleteDECLARE @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
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:
ReplyDeleteIF (COALESCE(@String1, 'String Value Is NULL') <> COALESCE(@String2, 'String Value Is NULL'))
PRINT 'Strings Differ'
This seems to work well enough.
Bravo to whoever posted the anonymous comment.
ReplyDeleteMy 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';