Converting a large decimal with percentage sign (%) to a smaller decimal

Question

I have the following data that I need to convert to a simple decimal. Currently, it is in varchar format. The data is in a column called Commission%

Commission% (currently Varchar)	Commission% V2 (needs to be decimal)
87.00000%	.87
95.00000%	.95

I have tried the following:

CAST(CAST(CONVERT(FLOAT,REPLACE([Commission %], ''%'', '''')) / 100 AS DECIMAL(10,6)) as DECIMAL(10,6))

BUT I get the following error message Error converting data type varchar to numeric. I was able to use something similar the other day but there were fewer trailing zero's at the time.

Answer 1

This is almost certainly due to bad data which is what you should expect when you store numbers as strings. Here is how you identify them:

SELECT [KeyColumn], [Commission%] 
  FROM dbo.BadColumnNamesWow
  WHERE [Commission%] IS NOT NULL
    AND TRY_CONVERT(decimal(20,10), REPLACE([Commission%], '%', '')) IS NULL;

Since you now say there are other patterns, it might make sense to just do this to identify all of the bad data:

SELECT * FROM dbo.BadColumnNamesWow
  WHERE [Commission%] IS NOT NULL
    AND PATINDEX('%[^0-9.]%', [Commission%]) > 0;

Then clean it up. This should be a lesson in why you never store numeric values in string columns.