| set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_Txt_SplitTAB]
(
@sInputList VARCHAR(8000)
, @Delimiter CHAR(1) = ','
)
RETURNS @List TABLE (Item VARCHAR(30)) WITH SCHEMABINDING
AS BEGIN
DECLARE @Item Varchar(30)
DECLARE @Pos int
, @NextPos int
, @LenInput int
, @LenNext int
, @DelimLen int
SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter)
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END
While @Pos <= @LenInput and @NextPos > 0 BEGIN
IF @NextPos > @Pos BEGIN
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
INSERT INTO @List SELECT @Item
END
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)
IF Len(@Item) > 0
INSERT INTO @List SELECT @Item
RETURN
END | | |