Issue converting varchar to INT in sql server -

i have seen question on stackoverflow, seems there wide number of solutions tailored situation. seems have unique situation far can tell. running sql statement

use ist_ca_2_batch_conversion go --t-sql script populate match type column declare @matchtype varchar(16), @pk varchar(500), @careturncode varchar(255),  @caerrorcodes varchar(255)  declare cursor1 cursor fast_forward select  ["ref#"], ["return code"], ["error codes"] cacodes2matchtype  open cursor1 fetch next cursor1 @pk,@careturncode,@caerrorcodes  while @@fetch_status = 0 begin  set @matchtype = dbo.getmatchtype(@careturncode,@caerrorcodes)  update cacodes2matchtype set [match type] = @matchtype ["ref#"] = @pk  fetch next cursor1 @pk,@careturncode,@caerrorcodes end close cursor1 deallocate cursor1 

it fail @

set @matchtype = dbo.getmatchtype(@careturncode,@caerrorcodes) 

here beginning code getmatchtype function:

-- batch submitted through debugger:      sqlquery14.sql|6|0|c:\users\b01642a\appdata\local\temp\~vs1c8e.sql  create function [dbo].[getmatchtype](@careturncode varchar(255), @caerrorcodes      varchar(255))  returns varchar(16)  begin    declare @matchtype varchar(16);   declare @errorcodes table(pos int, code char(2));   declare @country int;   -- 1 us, 2 canada   declare @numminorchanges int;   declare @nummajorchanges int;   declare @numsinglecodes int;   declare @returncode int;    declare @verified varchar(16);   declare @goodfull varchar(16);   declare @tentativefull varchar(16);   declare @poorfull varchar(16);   declare @multiplematch varchar(16);   declare @unmatched varchar(16);    set @verified = 'verified';   set @goodfull = 'good full';   set @tentativefull = 'tentative full';   set @poorfull = 'poor full';   set @multiplematch = 'multiple match';   set @unmatched = 'unmatched';    set @returncode = cast(@careturncode int); 

i error: msg 245, level 16, state 1, line 21 conversion failed when converting varchar value '"1"' data type int.

this error occurs @ last line of code segment have shown:

set @returncode = cast(@careturncode int); 

this code written colleague , supposedly had worked him. have had troubleshoot errors cannot debug one. understand alot of people create dbo.split function? don't know if option me in scenario. have tried setting @returncode varchar , getting rid of cast on @careturncode. result, debugger make past line raises issues rest of code. assuming there issue how casting @careturncode? appreciated.

the problem @careturncode contains non-numeric characters.

-- msg 245, level 16, state 1, line 21 conversion failed when converting varchar value '"1"' data type int. 

see, outer single quotes error message's formatting, inner double quotes in @careturncode value. solution here ensure variable contains numeric characters prior converting. if double quotes possibility, can quick , dirty fix this:

set @returncode = cast(replace(@careturncode, '"', '') int) 

if there more possibilities, multiple replace calls, or build better character-trimming function remove characters specify @ once yourself.