When are different Strings equal?

When are different Strings equal?

When working on the First Responder Kit recently, I needed to compare two strings and came across a bit of an oddity on how SQL compares strings. Let’s check it out.

First, put the following stored proc in to your database:

CREATE OR ALTER PROCEDURE AreStringsTheSame(
	@String1ToCheck NVARCHAR(10), 
	@String2ToCheck NVARCHAR(10)
) AS
BEGIN
	IF @String1ToCheck = @String2ToCheck
	BEGIN
		SELECT @String1ToCheck, @String2ToCheck, 'Yes'
	END
	ELSE
	BEGIN
		SELECT @String1ToCheck, @String2ToCheck,'No'
	END
END

Pretty simple, we’re going to take 2 strings in, compare them together. If they’re the same then we’ll have the word “Yes”, if they’re different, then we’ll have the word “No” come back.

So let’s play a game, what will the output be if I pass in the following? I’ve included my guesses

EXEC AreStringsTheSame N'Greg', N'Greg'	--Yes
EXEC AreStringsTheSame N'Greg', N'Dodd'	--No
EXEC AreStringsTheSame N'', N''		--Yes
EXEC AreStringsTheSame N' ', N' '	--Yes
EXEC AreStringsTheSame N' ', N''	--No
EXEC AreStringsTheSame N' Greg', 'Greg'	--No, leading space
EXEC AreStringsTheSame N'Greg ', 'Greg'	--No, trailing space

Did you lock in your guesses? Alright, let’s see how you did:

Ok, the first 4 are good, but what’s going on with the 5th result and the last result???

SQL Server somehow thinks that a single space is the same as an empty string. Is that a bug? Microsoft says no, it’s actually the ANSI/ISO Standard for SQL to ignore trailing spaces when comparing strings. It doesn’t matter if you have ANSI Padding turned on, or if you have VARCHAR or NVARCHAR or CHAR OR NCHAR fields, they all work the same.