The problem may be as simple as redefining your DECIMAL datatype conversion. Below I do the replace and ISNUMERIC check in one pass then only CAST values that meet that criteria.

--sample data

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[Col1] VARCHAR(50) NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT '123456789'

UNION

SELECT '123456789123456789'

UNION

SELECT '123,456,789,123,456,789'

UNION

SELECT NULL

UNION

SELECT ''

UNION

SELECT 'XYZ'

SELECT * FROM #TempTable

If I use DECIMAL(18,2)--the default and what you use in your question--I get the arithmetic overflow error using my sample data above. Change the size of the DECIMAL value and it accepts the larger number without an error.

If you are going to test whether or not the string is numeric you need to do the replacements first. In your original query your WHERE clause is filtering on the original varchar values before you've done the replacements or checked for valid numbers.

SELECT

ID

,CAST(Result AS DECIMAL(20,2)) AS Result

FROM

(

SELECT

ID

,REPLACE(Col1,',','') AS Result

,(CASE

WHEN NULLIF(Col1,'') IS NULL

THEN 0

WHEN ISNUMERIC(ISNULL(NULLIF(REPLACE(Col1,',',''),''),0)) = 1

THEN 1

ELSE 0

END) AS isNum

FROM

#TempTable

) r

WHERE

IsNum = 1