Oct 31, 2007

TSQL: Parsing delimited string into table

CREATE FUNCTION dbo.udf_ItemParse (
@Input VARCHAR(8000), @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE (
Item VARCHAR(50) ,
Pos int
)
AS
BEGIN

DECLARE @Item varchar(50)
DECLARE @StartPos int, @Length int
DECLARE @Pos int
SET @Pos = 0
WHILE LEN(@Input) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @Input)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@Input) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Pos = @Pos + 1
SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
END
ELSE
BEGIN
SET @Pos = @Pos+1
SET @Item = @Input
SET @Input = ''
END
INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
END
RETURN
END

kick it on DotNetKicks.com

1 comment:

Marcell said...

here is another version:

-- =============================================
-- Author: Marcell du Plessis
-- =============================================
Alter FUNCTION [dbo].[GetTableFromList_t]
(
-- Add the parameters for the function here
@DelimitedList varchar(Max),
@Delimeter varchar(5)
)
RETURNS
@Output TABLE (Item varchar(50))
AS
BEGIN
-- Fill the table variable with the rows for your result set

Declare @current_buff_pos smallint,
@next_dlm_pos smallint,
@dat_len smallint,
@var varchar(50)

Set @current_buff_pos = 0
While (@current_buff_pos < LEN(@DelimitedList))
Begin

Set @next_dlm_pos = Charindex(@Delimeter,@DelimitedList,@current_buff_pos+1)

If @next_dlm_pos<=0 Set @next_dlm_pos = LEN(@DelimitedList)+1

Set @var = Substring(@DelimitedList,@current_buff_pos+1,@next_dlm_pos - (@current_buff_pos+1) )


Set @current_buff_pos = @next_dlm_pos

if Not Exists(Select * From @Output Where Item=@var)
Insert Into @Output Values(@var)


End

RETURN
END
GO

-- Test

Select *
From
[dbo].[GetTableFromList_t] ('25,26,27,28',',')