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

2
votes

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..

2
sql-servertsql
Obviously, the problem is inside the view and you won't be able to fix it (under most circumstances) with your select query. You need to fix the view definition. - Gordon Linoff
How does your view look like? - Amnesh Goel
I think thats what needs to be done Gordon, I was hoping I could get some work around :) - edcoder

2 Answers

0
votes

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
0
votes

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.