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
