Character matching


For some background on this, let me refer you to Michael Kaplan's excellent post at http://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 http://msdn2.microsoft.com/en-us/library/ms143726.aspx).



 

Comments (0)

Skip to main content