Get ASCII codes from a VARCHAR SQL string

Today I faced a strange issue in SQL Server. I tried to select a specific row in SCCM table about OS Windows 7 Entreprise. So I typed the name of the OS in management studio, and ran a select on the proper table using a where clause on the OS name. Unexpectedly, no rows returned.
So I copied the name of the OS (from the SCCM table), and tested it against my own OS name. It can be summarized as below:

SELECT
  CASE WHEN 'Microsoft Windows 7 Entreprise' -- coming from SCCM
          = 'Microsoft Windows 7 Entreprise' -- what I typed
    THEN 'Equal'
    ELSE 'Not equal'
  END AS IsEqual

 

Wow... everyone who ever fought against special characters already knows the solution: a non-breakable space (or whatever) is present in one of the strings.

So I wrote a small query to inspect the ASCII characters of a string, and wished to share it. SO here it is:
 

DECLARE @Str VARCHAR(255) = 'Microsoft Windows 7 Entreprise' -- Coming from SCCM
;

WITH MyCounter AS
(
 SELECT 1 AS Idx
 UNION ALL
 SELECT a.Idx + 1 AS Idx
 FROM MyCounter a
 WHERE a.Idx < LEN(@Str)
)
SELECT Idx AS [Index], SUBSTRING(@Str, Idx, 1) AS [Character], ASCII(SUBSTRING(@Str, Idx, 1)) AS [AsciiCode]
FROM MyCounter
ORDER BY Idx asc

 

As expected, a non breakable space (ascii code 160) was present in SCCM table content, instead of standard whitespace (ascii code 32).

This small code can easily be turned into scalar valued function, if needed...
Hope it helps!