Home > SQL Server > SQL Integer list to table function

SQL Integer list to table function

 

Probably from SQLServerCentral.com

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

ALTER FUNCTION [dbo].[IntList_To_Table]

(@list ntext,

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

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

num int) 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 (num) VALUES(cast(@tmpval as int))

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

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

END

 

SET @leftover = @tmpstr

END

 

INSERT @tbl(num) VALUES (cast(ltrim(rtrim(@leftover)) as int))

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: