Extract values from a string using a delimiter

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[field]')
and xtype = 'FN')
drop function [dbo].[field]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function Field 
/*
27/04/07 - Written By Tim Sneller

The FIELD function returns a substring from a string expression. The substring is specified
by a delimiter character and index position.

The search delimiter is limited to any single character. Any additional characters are ignored.  
Syntax field(string, delimiter, Occurence, fields ) 


usage:
------------------------------------------------------------------------------
|FIELD(string, delimiter, Occurrence, fields)                          |
------------------------------------------------------------------------------

STRING

String is the source string for the FIELD operation

DELIMITER

The delimiter may be any character. If more than one character occurs in the delimiter expression, 
only the first character is used.

OCCURENCE

The occurrence defines the number of the occurrence of the substring that is to be returned. A negative 
number will return the LAST x fields, instead of the FIRST x fields.

If an occurence of 0 is given, the function will return the number of fields.

FIELDS

The field parameter defines the number of successive fields after the occurrence of the delimiter character
that are to be returned with the substring. The delimiters will be included as part of the returned value.

Examples:
declare @Data varchar(255)
set @Data = 'A,B,C,D,E'
print dbo.field(@data,',',2,2)

Returns: B,C

A negative start field returns the last fields

declare @Data varchar(255)
set @Data = 'A,B,C,D,E'
print dbo.field(@data,',',-2,2)

Returns: D,E

An Occurence value of 0 returns the number of values.
declare @Data varchar(255)
set @Data = 'A,B,C,D,E'
print dbo.field(@data,',',0,0)

Returns: 5

*/(@String varchar(255),
 @Delimiter varchar(10),
 @Occurrence int,
 @Num_Fields int)

returns varchar(255)

as

BEGIN

DECLARE@Fieldcount int,
@Loop_Count int,
@Return_String varchar(255),
@Start_Pos int,
@End_Pos int,
@End_Field int,
@Test varchar(10)

SET @Fieldcount = len(@String) - len(replace(@String,@Delimiter,'')) + 1
SET @Return_String = ''

IF @Occurrence = 0
BEGIN
SET @Fieldcount = len(@String) - len(replace(@String,@Delimiter,'')) + 1
SET @Return_String = @FieldCount
end

IF @Occurrence < 0
BEGIN
 IF @Num_Fields > abs(@Occurrence) SET @Num_Fields = abs(@Occurrence)
IF abs(@Occurrence) > @Fieldcount  
BEGIN
SET @Num_Fields = 0
end
Else
BEGIN
SET @Occurrence = @Fieldcount + @Occurrence + 1
END
end

IF @Num_Fields = 0 SET @Num_Fields = 1
IF @Num_Fields > @Fieldcount SET @Num_Fields = @Fieldcount
SET @End_Field = @Occurrence + @Num_Fields - 1
IF @End_Field > @Fieldcount SET @End_Field = @Fieldcount

SET @Num_Fields = @End_Field - @Occurrence
SET @Loop_Count = 1
SET @Start_Pos = 1

IF @Fieldcount = 1 and @Occurrence = 1 SET @Return_String = @String
IF @Fieldcount = 1 and @Occurrence > 1 SET @Return_String = ''

IF @Fieldcount > 1

BEGIN
while @Loop_Count <= @End_Field 
BEGIN
SET @End_Pos = charindex(@Delimiter,@String,@Start_Pos)
IF @End_Pos = 0 
BEGIN
SET @Loop_Count = @End_Field
SET @End_Pos = len(@String) + 1
END
  IF  @Loop_Count >= @Occurrence 
begin
SET @Return_String = @Return_String + substring(@String,@Start_Pos,@End_Pos - @Start_Pos) 
 if @Loop_Count < @End_Field 
BEGIN
set @Return_String = @Return_String + @delimiter
END
end
SET @Loop_Count = @Loop_Count + 1
SET @Start_Pos = @End_Pos + 1
end
end
RETURN @Return_String

END --end function Field