SQL Error: Conversion failed when converting the varchar value ',' to data type int

Question

I'm breaking my head over this one. I keep getting the error. I followed the resolutions on the other questions but nothing works for me. I really hope someone can correct me here.

All I want is to get the data out of a view

SELECT Table_view.Information
FROM Table_View

but I keep getting this error

Conversion failed when converting the varchar value ',' to data type int

I tried the following:

SELECT Table_View.Information
FROM Table_View
WHERE Table_View.Information NOT LIKE '%,%'

SELECT Table_View.Information
CASE WHEN CAST(Table_View.Information AS INT) = 1 THEN 'space' 
ELSE CONVERT(VARCHAR, Table_View.Information) END
FROM Table_View

SELECT CAST (REPLACE(REPLACE(Table_View.Information, ' ,', ' '),'/', '') AS VARCHAR)
FROM Table_View

SELECT CONVERT(VARCHAR, REPLACE(REPLACE(Table_View.Information, ' ,', ' '),'/', ''))
FROM Table_View

SELECT Table_View.Information
CASE 
WHEN Table_View.Information NOT LIKE '%,%' THEN CAST(Table_View.Information AS VARCHAR)
END 

FROM Table_View 

None of the above helps me resolve the issue. Any suggestions? I tried cast, convert, case, replace..

Answer 1

Clearly you should use try_convert() or try_cast() if 2012+. Rather than throwing an error, a NULL will be returned if the conversion fails.

That said, convert(money,...) tends to be a little more forgiving

Example

Declare @Table table (SomeCol varchar(50))  
Insert Into @Table values 
 ('$25.25')
,('-  252,525.25')

Select AsMoney = convert(money,SomeCol)  -- int would fail
 From  @Table

Returns

AsMoney
25.25
-252525.25

Answer 2

WHERE Table_View.Information NOT LIKE '%,%'

The above code implies that Information is a character column, as it would be impossible to convert a comma into a number.

CASE WHEN CAST(Table_View.Information AS INT) = 1 THEN 'space' 

There error is here, as CAST does not provide a return value such that it can be compared to the number 1, and if the value contains a comma then any attempt to convert to an int would result in a type conversion error.

Try using ISNUMERIC() or TRY_CAST() in place of where you have CAST.