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 function to remove any white spaces which were present at the beginnend and the end of the value. Unfortunately the characters in the string didn’t get removed with these functions. This let me to creating a little script to get the different character strings.
DECLARE @value VARCHAR(50)
DECLARE @sql NVARCHAR(max)
DECLARE @i INT
SET @value = ' 11452.72 '
SET @i = 0
SET @sql = ''
WHILE @i < Datalength(@value)
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)
SELECT @sql += ' UNION ALL '
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.