Home > SQL Server > SQL two column list to two column table

SQL two column list to two column table

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

 

ALTER
FUNCTION [dbo].[CharList_To_Table_2_Value]


(@list ntext,

@delimiter nchar(1)
= N‘,’)


RETURNS @tbl TABLE
(listpos int
IDENTITY(1, 1)
NOT
NULL,


str
varchar(4000),

                             str2 varchar(4000)

            )
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000),

             @tmpval2 nvarchar(4000),

             @num        int

 

     SET @num = 0


SET @textpos = 1


SET @leftover =


WHILE @textpos <=
datalength(@list)
/ 2


BEGIN


SET @chunklen = 4000
datalength(@leftover)
/ 2


SET @tmpstr = @leftover +
substring(@list, @textpos, @chunklen)


SET @textpos = @textpos + @chunklen

 


SET @pos =
charindex(@delimiter, @tmpstr)

 


WHILE @pos > 0


BEGIN

            if @num = 0

            begin

     SET @tmpval =
ltrim(rtrim(left(@tmpstr, @pos 1)))

                set @num = 1

            end

            else

            begin

                SET @tmpval2 =
ltrim(rtrim(left(@tmpstr, @pos 1)))

     INSERT @tbl (str, str2)
VALUES(@tmpval, @tmpval2)

                set @num = 0

            end

            
 


SET @tmpstr =
substring(@tmpstr, @pos + 1,
len(@tmpstr))


SET @pos =
charindex(@delimiter, @tmpstr)


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, str2)
VALUES
(@tmpval,
ltrim(rtrim(@leftover)))


RETURN


END

 


 

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: