Author: Eric Liew
/*
This is a SQL UserFunction that takes a string array and converts it to an array to allow you to process the array. Purpose is to allow you to make one call to the database if you have to add multiple records instead of processing single records at a time
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SplitItems]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_SplitItems]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function fn_SplitItems (@ItemArray varchar(2048), @Delimiter varchar(10) = ',')
returns @ItemList TABLE
(
Item varchar(50),
Ordinal int
)
as
begin
DECLARE
@DelimiterLength int,
@IdxStart int,
@IdxEnd int,
@Item varchar(50),
@Ordinal int
select @DelimiterLength = DATALENGTH(@Delimiter)
select @IdxStart = 1
select @Ordinal = 0
while 1=1
begin
select @IdxEnd = CHARINDEX(@Delimiter, @ItemArray, @IdxStart)
if @IdxEnd = 0
select @Item = LTRIM(RTRIM(substring(@ItemArray, @IdxStart, DATALENGTH(@ItemArray) - @IdxStart + 1)))
else
select @Item = LTRIM(RTRIM(substring(@ItemArray, @IdxStart, @IdxEnd - @IdxStart)))
select @Ordinal = @Ordinal + 1
insert @ItemList (Item, Ordinal) values (@Item, @Ordinal)
if @IdxEnd = 0 break
select @IdxStart = @IdxEnd + @DelimiterLength
end
return
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
