Extract values from a string using a delimiter

,

There are many occasions in programs where you need to manipulate strings of characters that are delimited by a particular character, such as a comma, or a space. This function enables you to extract a substring from the string at a specified occurence of the delimter.

declare @Data varchar(255)

set @Data = 'A,B,C,D,E'

print dbo.field(@data,',',2,2)

Result:-

B,C

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

Rate

4.17 (6)

Share

Share

Rate

4.17 (6)