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%

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.

CodePudding user response：

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.