Character matching

For some background on this, let me refer you to Michael Kaplan's excellent post at https://blogs.msdn.com/michkap/archive/2005/01/11/350482.aspx.

Sometimes you will be an environment in which string comparisons don't quite work like you expect them to. One thing to quickly rule out is that your strings actually have the values you think you're looking for.

select
cast('(text)' as nvarchar(max)),
cast(0x00 as varbinary(max))

union all

select firstname, cast(firstname as varbinary(max))
from person.contact where contactid = 2

union all

select N'Catherine', cast(N'Catherine' as varbinary(max))

union all

select 'Catherine', cast('Catherine' as varbinary(max))

If you run with on the AdventureWorks database, you'll get these results back

(text) 0x00
Catherine 0x43006100740068006500720069006E006500
Catherine 0x43006100740068006500720069006E006500
Catherine 0x436174686572696E65

All I'm doing here is showing the encoding for the string 'Catherine' as it appears in the database, and the one I'm typing into SSMS. Everything agrees, which is all good.

For fun, I've also added the non-Unicode varbinary representation. If you're dealing with Unicode and non-Unicode strings, you can try encoding strings one way or another to see if all the things are working correctly. Note that SQL Server will do conversions for you automatically - in my case,

select * from person.contact where firstname='Catherine'

is equivalent to

select * from person.contact where firstname=N'Catherine'

This trick is useful to look for exact matches - for a whole lot more of information, you can read up on 'code pages [SQL Server], about code pages' on BOL (also available at https://msdn2.microsoft.com/en-us/library/ms143726.aspx).