Function to Split a Delimited String and Return a Field

Use below UDF to split a delmited string, and return the index value.


CREATE FUNCTION dbo.GetValueOfSplitStringByIndex (@Sep Char(1), @StrTemp VARCHAR(1540), @Index INT)
RETURNS VARCHAR(1540)
AS
-- Takes a @Sep separated string, turns it into a list internally and returns the value from that
-- list found at the @Index location.  Null is return if that @Index does not exist.
-- Sample usage (testing):
-- select dbo.GetValueOfSplitStringByIndex('-','200-2505-0000-01-149-101-10',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',2)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',3)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',4)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l,',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc',0)
BEGIN
DECLARE @Pos INT
DECLARE @Piece VARCHAR(500)
DECLARE @Curr_Index INT
SET @Curr_Index = 0
-- Need to tack a delimiter to the end of the input string if one doesn't exist
if right(Rtrim(@Sep),1) <> @Sep
SET @StrTemp = Rtrim(@Sep)  + @Sep


SET @Pos =  PatIndex('%' + @Sep + '%' , @Sep)
WHILE @Pos <> 0
BEGIN
SET @Piece = Left(@Sep, @Pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
if @Curr_Index=@index
return cast(@Piece as varchar(1540))


SET @StrTemp = stuff(@Sep, 1, @Pos, '')
SET @Pos =  patindex('%' + @Sep + '%' , @Sep)
SET @Curr_Index = @Curr_Index + 1
END
RETURN Null
END


Example:
SELECT dbo.GetValueOfSplitStringByIndex(',','ABC,GOT,klmnop,l0',2)


Result:
'klmnop'