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

I have been struggling for a couple of days now before turning here.

I'm trying to build a function, StringToInteger, that converts a textstring to integer. I have researched the web and gotten so far that I have some code that convert one string, entered in a variable, to an integer.

DECLARE @YourString   varchar(500)
SELECT @YourString='select'

;WITH AllNumbers AS
(
SELECT 1 AS Number
        UNION ALL
        SELECT Number+1
    FROM AllNumbers
    WHERE Number<LEN(@YourString)
)
SELECT
   (SELECT
        RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
 开发者_JAVA百科       FROM AllNumbers
        ORDER BY Number
        FOR XML PATH(''), TYPE
   ).value('.','varchar(max)') AS NewValue

 --OPTION (MAXRECURSION 500) --<<needed if you have a string longer than 100

I also found some code on how to query a table, TableB, and send its result to a function and have the whole set run.

SELECT
*
FROM
TableB
CROSS APPLY
dbo.StringToInteger(TableB.ColumnWithText) AS IntegerOutOfText 

My question is, how should the code at the top be adjusted so that it is loaded into the db as a function when run? How much I try, I cant get SQL Server import it as a function.

And my other question is, how should the bottom code be adjusted so that it calls the function and have it convert the whole query's result set to a set numbers number.

Best regards Daniel

The function could look like this:

create function StringToInteger(@YourString varchar(500))
returns varchar(1500) as
begin

  declare @Ret varchar(1500);

  WITH AllNumbers AS
  (
  SELECT 1 AS Number
          UNION ALL
          SELECT Number+1
      FROM AllNumbers
      WHERE Number<LEN(@YourString)
  )
  SELECT @Ret =
     (SELECT
          RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)
          FROM AllNumbers
          ORDER BY Number
          FOR XML PATH(''), TYPE
     ).value('.','varchar(max)');

  return @Ret;
end

Use like this:

DECLARE @YourString   varchar(500)
SELECT @YourString='select'

select dbo.StringToInteger(@YourString)

Use with a table like this:

declare @T table (Col1 varchar(10))
insert into @T values('select')

select dbo.StringToInteger(Col1)
from @T

Do you really have some substring to parse out before you do the conversion?

what about TO_NUMBER()? that seems alot simpler.