Strextract UDF - Retrieves String Between Two Delimiters

The ufn_StrExtract() UDF for SQL Server is similar to the VFP StrExtract() function.

$SAMPLECODE$


-- ufn_StrExtract ---
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufn_StrExtract')
DROP FUNCTION ufn_StrExtract
GO

CREATE FUNCTION ufn_StrExtract (
@StrIn varchar(8000),
@StartDelim varchar(128),
@EndDelim varchar(128),
@Flag int=0)

RETURNS varchar(8000)
AS
/* Extracts part of a string between two delimiters
Parameters:
@StrIn - source character expression
@StartDelim, @EndDelim - start and end delimiters
@Flag - modifies function behavior:
2 - End delimiter not required. If it's not found returns the rest of the string
4 - Delimiters are included in result
*/
BEGIN
DECLARE @StartPos int, @EndPos int, @LenStartDelim int, @LenEndDelim int
DECLARE @StrOut varchar(8000)

-- DATALENGTH() returns actual length including trailing spaces
SET @LenStartDelim = DATALENGTH(@StartDelim)
SET @LenEndDelim = DATALENGTH(@EndDelim)

IF @LenStartDelim = 0
-- If Start delimiter is empty, extract from the beginning of the string
SET @StartPos = 1
ELSE
-- Find Start delimiter position in the string
SET @StartPos = CHARINDEX(@StartDelim, @StrIn)

IF @StartPos > 0 BEGIN
-- Look for End delimiter position
IF @LenEndDelim = 0
-- If End delimiter is empty, extract to the end of the string
SET @EndPos = DATALENGTH(@StrIn)
ELSE BEGIN
-- Find End delimiter position in the string
SET @EndPos = CHARINDEX(@EndDelim, @StrIn, @StartPos + 1) - 1
IF (@EndPos 0
-- The End delimiter is not found but flag 2 is set
-- extract to the end of the string
SET @EndPos = DATALENGTH(@StrIn)
END
END

IF (@StartPos > 0) AND (@EndPos > 0) BEGIN
-- We found both delimiters
IF (@flag & 4)=0
-- Flag 4 is not set, don't include Start delimiter into result
SET @StartPos = @StartPos + @LenStartDelim
IF @EndPos 0
-- Flag 4 is set, include End delimiter into result
SET @EndPos = @EndPos + @LenEndDelim

-- Extract substring
SET @StrOut = SUBSTRING(@StrIn, @StartPos, @EndPos - @StartPos + 1)

END
ELSE
-- One of the delimiters or both not found, return empty string
SET @StrOut = ''

RETURN @StrOut
END
GO

-- Sample of using ufn_StrExtract UDF
SELECT dbo.ufn_StrExtract('The quick brown fox jumps over the lazy dog', 'fox', 'dog', 4)
SELECT dbo.ufn_StrExtract('The quick brown fox jumps over the lazy dog', 'fox', 'dog', DEFAULT)