Fun With SQL Equality

T-SQL has some interesting concepts regarding string equality:

SELECT
	CASE
		WHEN N'String with spaces          ' = N'String with spaces' THEN 1
		ELSE 0
	END;

Trailing spaces are not enough to consider a string to be unique.

SELECT
	CASE
		WHEN N'179³' = N'1793' THEN 1
		ELSE 0
	END;

Subscripts and superscripts are not enough to consider a string to be unique.

If you need to put a unique index on an NVARCHAR column, the best bet might be to add a SHA1 hash of the string. If you are okay with case-sensitive strings, you can use HASHBYTES(‘SHA1’, @YourString) to do the trick.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s