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
here is another version:
ReplyDelete-- =============================================
-- 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',',')