How to convert varchar to integer in SQL Server stored procedure?

Question

I need to write stored procedure that gets string. Each char in string have to be converted to int type and the converted type have to be inserted to the table.

Answer 1

How about writing a UDF (http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html) to split the delimited string and then calling it within your Stored Procedure.

CREATE FUNCTION [dbo].[SplitUsingXML] 
( 
    @String VARCHAR(MAX),
    --If your delimiter is multi character
    --then change it as VARCHAR with appropriate length 
    @Delimiter CHAR(1) 
)
RETURNS @Results TABLE
(
    parsedValue VARCHAR(MAX)
)   
AS
BEGIN
    DECLARE @xml XML
    SET @XML = N'<Content><row>' + REPLACE(@String, @Delimiter, '</row><row>') + '</row></Content>'

    --If your individual value length within the CSV can be more than 25 characters
    --then you might want to increase it in the below statement
    --pls note it won't throw an error if its more than 25 characters 
    --just that it would truncate and show only the first 25 character :)
    INSERT INTO @Results(parsedValue)
    SELECT row.value('.','VARCHAR(25)') as parsedValue 
    FROM @xml.nodes('//Content/row') AS RECORDS(row)

    RETURN
END
GO

--Usage
SELECT Cast(parsedValue as INT) 
FROM [dbo].[SplitUsingXML] ('1,2,3,4',  ',')
GO

Answer 2

The numbers table solution I posted over on this question will be your most efficient solution. Print bullet before each sentence + new line after each sentence SQL will patch code in once I'm home

Edit

The base unit of work is the inline table-valued function. You might have heard about TVF and how they suck in SQL Server but that relates to the multi-statement types. Inlines are fine as the optimizer can make sense of them and not make terrible plans.

dbo.StringSplit returns a single column (varchar) table with the values split based on the supplied delimiter. You can cut down the lines of code required (derived tables L0 to L5) if you already have numbers table or a fast number generator in your data. I assume you don't. The technique of using a numbers table to split data is not mine but I trust the SQL luminaries who have done the analysis.

You asked for a proc so I have supplied dbo.StringSplitToInts to comply but all it's doing is calling the TVF with the proper parameters. You can extract the select statement and cast into inline code or wherever you need it.

-- This function splits a delimited string with good performance
-- characteristics
CREATE FUNCTION dbo.StringSplit
(
    @input varchar(8000)
,   @delimiter char(1) = ','
)
RETURNS
    table
RETURN
-- L0 to L5 simulate a numbers table
-- http://billfellows.blogspot.com/2009/11/fast-number-generator.html
WITH L0 AS
(
    SELECT
        0 AS C
    UNION ALL
    SELECT
        0
)
, L1 AS
(
    SELECT
        0 AS c
    FROM
        L0 AS A
        CROSS JOIN L0 AS B
)
, L2 AS
(
    SELECT
        0 AS c
    FROM
        L1 AS A
        CROSS JOIN L1 AS B
)
, L3 AS
(
    SELECT
        0 AS c
    FROM
        L2 AS A
        CROSS JOIN L2 AS B
)
, L4 AS
(
    SELECT
        0 AS c
    FROM
        L3 AS A
        CROSS JOIN L3 AS B
)
, L5 AS
(
    SELECT
        0 AS c
    FROM
        L4 AS A
        CROSS JOIN L4 AS B
)
, NUMS AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM
        L5
)
, SOURCE_DATA (ID, content) AS
(
    -- This query simulates your input data
    -- This implementation could be simplified as our function
    -- only accepts 1 row of data but this could be applied to 
    -- any category of problem, not just a single line of input
    SELECT 1, @input 
)
, MAX_LENGTH AS
(
    -- this query is rather important. The current NUMS query generates a 
    -- very large set of numbers but we only need 1 to maximum lenth of our
    -- source data. We can take advantage of a 2008 feature of letting
    -- TOP take a dynamic value
    SELECT TOP (SELECT MAX(LEN(SD.content)) AS max_length FROM SOURCE_DATA SD)
        N.number
    FROM
        NUMS N
)
, MULTI_LINES AS
(
    -- This query will make many lines out a single line based on the supplied delimiter
    -- Need to retain the ID (or some unique value from original data to regroup it
    -- http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
    SELECT 
        SD.ID
    ,   LTRIM(substring(SD.content, Number, charindex(@delimiter, SD.content + @delimiter, Number) - Number)) AS lines
    FROM
        MAX_LENGTH
        CROSS APPLY
            SOURCE_DATA SD
    WHERE
        Number <= len(SD.content)
        AND substring(@delimiter + SD.content, Number, 1) = @delimiter
)
SELECT
    ML.lines
FROM
    MULTI_LINES ML
GO

-- This is overkill as the function is more versatile but
-- in the spirit of delivering what was asked for, this proc
-- calls the function and casts the data to the appropriate type
CREATE PROCEDURE dbo.StringSplitToInts
(
    @input varchar(8000)
,   @delimiter char(1) = ','
)
AS
BEGIN
    SET NOCOUNT ON
    SELECT
        CAST(SS.lines AS int) AS int_tokens
    FROM
        dbo.StringSplit(@input, @delimiter) SS

END
GO

-- Over 9000!
EXECUTE dbo.StringSplitToInts '100,200,300,500,9000'

Answer 3

Use XML

Try this:

DECLARE @S varchar(max), @Split char(1), @X xml   

SELECT @S = '1,2,3,4,5', @Split = ',' 
SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@S,@Split,'</s> <s>') + '</s>   </root> ') 
SELECT [Value] = T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c)

Answer 4

from my mind : ( not tested)

to convert :

select cast ( @myString as int)

to split chars into table of ints :

create procedure aaa
@d nvarchar(max)

as
declare @i int
set @i=0
 declare @t table(val int)

while @i<=len(@d)



begin
insert into @t (val) select cast (SUBSTRING(@d, i, 1) as int)
set @i=@i+1
end

Answer 5

You could use a recursive CTE to generate a list of character positions in the string:

create procedure dbo.AddInts(
    @numbers varchar(max))
as
; with  list as
        (
        select  1 as pos
        where   len(@numbers) > 0
        union all
        select  pos + 1
        from    list
        where   len(@numbers) > pos
        )
insert  YourTable
        (col1)
select  cast(substring(@numbers, list.pos, 1) as int)
from    list
go