Thursday, 10 May 2012

How to see all string characters with their ASCII codes

When i analyzes some databases, i can see differences in stored procedures. SSMS 2012 suggests me to change CR/LF new line type. What would it can be?
To analyze differences, i wrote very simple code to see all SQL module definition characters together with their ASCII code values.
DECLARE @Object_Name VARCHAR(256)= 'Your object'
SELECT  @Definition = m.definition
       FROM    sys.all_sql_modules M
       WHERE   OBJECT_NAME (M.object_id) = @Object_Name;
WITH    H AS ( SELECT   SUBSTRING(@Definition, 1, 1) AS Char ,
                        ASCII(SUBSTRING(@Definition, 1, 1)) AS Code ,
                        1 AS Position
               UNION ALL
               SELECT   SUBSTRING(@Definition, H2.Position + 1, 1) AS Char ,
                        ASCII(SUBSTRING(@Definition, H2.Position + 1, 1)) AS Code ,
                        H2.Position + 1 AS Position
               FROM     H H2
               WHERE    H2.Position < LEN(@Definition)
    SELECT  *
    FROM    H
The result should look like this:
You can set @Definition variable as your custom string and see all characters and ASCII codes if you need.