Get ASCII characters from string

I had a situation where I got an error converting a value to a decimal which had some white text and a valid number.

During the conversion, I used the LTRIM and RTRIM functions to remove any white spaces which were present at the beginning and the end of the value.

Unfortunately, the characters in the string didn’t get removed with these functions.

This led me to create a little script to get the different character strings.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DECLARE @value VARCHAR(50) 
DECLARE @sql NVARCHAR(max) 
DECLARE @i INT 

SET @value = '  11452.72 ' 
SET @i = 0 
SET @sql = '' 

WHILE @i < Datalength(@value) 
  BEGIN 
      SELECT @sql += 'SELECT ' + Cast(@i AS VARCHAR) 
                     + ' AS ''Position'',' + 'SUBSTRING(''' + @value 
                     + ''', (' + Cast(@i AS NVARCHAR) 
                     + ' + 1), 1) AS ''String Value'',' 
                     + 'CAST(ASCII(SUBSTRING(''' + @value + ''', (' 
                     + Cast(@i AS NVARCHAR) 
                     + ' + 1), 1)) AS VARCHAR) AS ''ASCII value''' 

      SELECT @i += 1 

      IF @i < Datalength(@value) 
        BEGIN 
            SELECT @sql += ' UNION ALL ' 
        END 
  END 

EXEC sp_executesql @sql

This gave me the following result:

I knew the ASCII value 32 is a space and these characters were removed during the process.

But as you can see the values at the end are not spaces, tabs, or any other kind of white space character.

This helped me find out that something went wrong with the export of the files which I would never have found without knowing the reason why the value didn’t convert to decimal.

For more information about the ASCII function you can follow this link I hope this script can help you too.