Home > SQL Server > SQL string list to table function

SQL string list to table function

From SQLServerCentral.com

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

ALTER
FUNCTION [dbo].[CharList_To_Table]


(@list ntext,

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


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


str
varchar(4000),

nstr nvarchar(2000))
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

 


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


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


INSERT @tbl (str, nstr)
VALUES(@tmpval, @tmpval)


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


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


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, nstr)
VALUES
(ltrim(rtrim(@leftover)),
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: