Search

Monday, October 1, 2012

Convert Delimited String to Table

Sometime we may need to convert delimited string to table. From below function we can do this conversion:


CREATE FUNCTION Func_String_To_Table
(@strTemp VarChar(1000))
Returns @Table Table (VAL int)
AS
BEGIN
    SET @strTemp = @strTemp + ','
    DECLARE @curr_char VarChar(1)
    DECLARE @char VarChar(6)
    DECLARE @len int
    DECLARE @num int
    DECLARE @counter int
    SET @char = ''
    SET @curr_char = ''
    SET @counter = 1
    SET @len = len(@strTemp) 

    WHILE @counter <= @len 
    BEGIN
        SET @curr_char = substring(@strTemp, @counter, 1)
        IF (@curr_char <> ',')
        BEGIN
            SET @char = @char + @curr_char         
        END
        ELSE
        BEGIN  
            SET @num = convert(int, @char)         
            INSERT INTO @Table VALUES(@num)
            SET @char = ''
        END
       SET @counter = @counter + 1
    END
    RETURN
END

No comments:

Post a Comment