sql - How to create a function that converts a set of strings to integer -

i have been struggling couple of days before turning here.

i'm trying build function, stringtointeger, converts textstring integer. have researched web , gotten far have code convert 1 string, entered in variable, integer.

declare @yourstring   varchar(500) select @yourstring='select'  ;with allnumbers ( select 1 number         union         select number+1     allnumbers     number<len(@yourstring) ) select    (select         right('000'+convert(varchar(max),ascii(substring(@yourstring,number,1))),3)         allnumbers         order number         xml path(''), type    ).value('.','varchar(max)') newvalue   --option (maxrecursion 500) --<<needed if have string longer 100 

i found code on how query table, tableb, , send result function , have whole set run.

select * tableb cross apply dbo.stringtointeger(tableb.columnwithtext) integeroutoftext  

my question is, how should code @ top adjusted loaded db function when run? how try, cant sql server import function.

and other question is, how should bottom code adjusted calls function , have convert whole query's result set set numbers number.

best regards daniel

the function this:

create function stringtointeger(@yourstring varchar(500)) returns varchar(1500) begin    declare @ret varchar(1500);    allnumbers   (   select 1 number           union           select number+1       allnumbers       number<len(@yourstring)   )   select @ret =      (select           right('000'+convert(varchar(max),ascii(substring(@yourstring,number,1))),3)           allnumbers           order number           xml path(''), type      ).value('.','varchar(max)');    return @ret; end 

use this:

declare @yourstring   varchar(500) select @yourstring='select'  select dbo.stringtointeger(@yourstring) 

use table this:

declare @t table (col1 varchar(10)) insert @t values('select')  select dbo.stringtointeger(col1) @t