Part of the issue may be using ISNUMERIC. This function is barely passable as a validation or filtering tool. Take the fine example that Steve posted and add 1 more row to the insert.

SELECT '123e4'

Like this to see the whole picture.

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'

union

SELECT '123e4'

--SELECT * FROM #TempTable

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

The last value will pass the ISNUMERIC check but it still can't be cast as a decimal.