CODEWITHSUNDEEP
sql
Amit
Amit

Reputation: 7045

convert comma separated string to sql table in SP

I have a comma separated value as nvarchar in sql. which i need to convert into table. Please advise.

example of string

'f143bda4-a917-479c-8360-b63943b91d91,f312f49b-203e-4bba-a74e-82ea356ed6d3'

I am using sql server 2005

Upvotes: 0

Views: 621

Answers (1)

Yves M.
Yves M.

Reputation: 3318

Are you looking for something like this...

FUNCTION [dbo].[fx_Split]
(
    @text varchar(max),
    @splitChar char(1)
)
RETURNS 
@Result TABLE 
(
    RowIndex int identity(1,1),
    SplitText varchar(max) -- choose your type here...
)
AS
BEGIN

    DECLARE @index int SET @index = 0
    DECLARE @SplitText varchar(max) SET @SplitText = ''
    DECLARE @TempText varchar(max) SET @SplitText = ''

    SET @index = CHARINDEX(@splitChar, @text) 
    SET @TempText = @text

    WHILE(@index > 0)
    BEGIN

        INSERT INTO @Result VALUES (SUBSTRING(@TempText, 1, @index-1))

        SET @TempText = LTRIM(SUBSTRING(@TempText, @index + 1, LEN(@TempText)))

        SET @index = CHARINDEX(@splitChar, @TempText) 

    END 

    INSERT INTO @Result VALUES (@TempText)

    RETURN 
END

Upvotes: 1

Related Questions