TSQL - Varbinary Cast

We use a web application to manage much of our system security. Much like Windows, we use a role based security system (think user groups). When creating new user groups we often reference standard user group names. Some of these names are stored in a Word document. So, when a hyphen (i.e., short dash) is entered into Word followed by a dash, it is automatically converted into an "en dash". This is when the single dash is expanded and looks "wider" than normal.

Since we run some scheduled tasks to regularly perform functions based on user group names this automatic formatting feature of Word becomes an issue. The script looks explicitly for the hyphen (ASCII decimal code 45/hexidecimal code 2D) but finds the en dash (ASCII decimal code 150/hexidecimal code 96). For reference, visit this site:

http://www.ascii-code.com/

to get an idea of what these characters are.

After suspecting this was the root cause some updates were not occurring, I wanted to find a way to validate my hunch via SQL directly. Knowing we were dealing with two characters that would appear similar in the web application, but, could be identified as unique via the hex values. I pulled up this link:

http://stackoverflow.com/questions/219245/converting-a-string-to-hex-in-sql

which lead me to come up with this query (the CAST is highlighted in orange):

SELECT usergroupname, CAST(usergroupname AS VARBINARY) FROM MyDatabase.dbo.MyTable

This provided me with a side by side string-to-hex relationship. I then created a new group with the hyphen (0x2D) and reran the query. I was able to use this as a good way to provide an explanatory email to my team members as to why they needed to be careful with the Word cut-and-paste technique.