Slice out string from within specified delimiters
Scalar function 'f_delimited' slices out of a passed-in string, from a specified position inside the string, that segment of it which is delimited on one or both sides by a specified delimiter.
Example: Print out a segment of a 'syscomments.text' column for a stored procedure 'p_proc', representing a line of code (i.e., the segment of 'syscomments.text' between carriage returns) containing the string '@retc'.
Given: @line_of_code VARCHAR (200), @pos INT, @delim CHAR (1)
SELECT @delim = CHAR (13) -- carriage return
, @pos = CHARINDEX ('@retc', text)
, @line_of_code = dbo.f_delimited
(@f_string = text
, @f_delim = @delim
, @f_pos = @pos)
FROM syscomments
WHERE id = OBJECT_ID ('p_proc')
AND colid = 1
IF EXISTS (SELECT 1
FROM sysobjects
WHERE xtype = 'FN'
AND name = 'f_delimited')
DROP FUNCTION f_delimited
GO
CREATE FUNCTION dbo.f_delimited (@f_string VARCHAR (8000)
, @f_delim VARCHAR (4000)
, @f_pos INT)
RETURNS VARCHAR (8000)
/*
||======================================================================
|| Date created: 7/17/2002 (Dise)
||
|| Purpose: This function was coded with the following problem in mind:
|| Suppose you have a long character string that contains
|| lines of code (each delimited with ASCII 13, the carriage-
|| return value). Given an address within the string, you
|| want to be able to return the specific line of code that
|| the address falls within.
||
|| The function returns a segment, contained within a
|| specified string, that happens to be delimited by the
|| specified delimiter. An address within this string is
|| passed in, and if the address happens to be bounded by
|| the delimiter, only the segment inside the delimiters
|| is passed back.
||
|| If the specified address has a delimiter to its left,
|| the returned segment will start at the byte following the
|| delimiter; otherwise, the returned segment will start at
|| the beginning of the string.
||
|| If the specified address has a delimiter to its right,
|| the returned segment will end at the byte preceeding the
|| delimiter; otherwise, the returned segment will end at
|| the end of the string.
||
|| If the specified position within the string happens to be
|| equal to the delimiter, this occurrence of the delimiter
|| is not counted, but occurrences of the delimiter to its
|| right or left do count.
||
|| If the specified delimiter is not found in the string, the
|| entire string is returned.
||
|| If the length of the delimiter is longer than the length
|| of the string, the entire string is returned.
||
|| If the specified position is zero, less than zero, or
|| greater than the length of the string, the entire string
|| is returned.
||
|| Parameters:
|| @f_string -- the specified string
|| @f_delim -- the specified delimiter
|| @f_pos -- a position, or address, within the string.
||
|| Examples:
||
|| Given:
|| @f_string: 'The fox said, "Howdy, fellows!" when he looked up.'
|| @f_delim: '"' (double quote)
||
|| (i) Specified position is delimited on both sides:
||
|| @f_pos: any value between 16 and 30
|| RETURNS: 'Howdy, fellows!'
||
|| (ii) Specified position is delimited on left side:
||
|| @f_pos: any value between 32 and 50
|| RETURNS: ' when he looked up.'
||
|| (iii) Specified position is delimited on right side:
||
|| @f_pos: any value between 1 and 29
|| RETURNS: 'The fox said, '
||
|| (iv) Specified position is same address as left-most delimiter:
||
|| @f_pos: 15
|| RETURNS: 'The fox said, "Howdy, fellows!'
||
|| (v) Specified position is same address as right-most delimiter:
||
|| @f_pos: 31
|| RETURNS: 'Howdy, fellows!" when he looked up.'
||
|| Returns: VARCHAR (8000)
||======================================================================
*/AS
BEGIN
/*
||====================================================================
|| Local variable declarations...
||====================================================================
*/ DECLARE @segment VARCHAR (8000)
, @revstr VARCHAR (8000)
, @revdelim VARCHAR (8000)
, @lopos INT
, @hipos INT
/*
||====================================================================
|| Initializations...
||====================================================================
*/ SELECT @segment = @f_string
/*
||====================================================================
|| Only look for a delimited string segment if:
|| 1. The delimiter is smaller than the actual string.
|| 2. The specified position within the string is a positive number
|| between 1 and the length of the string.
||====================================================================
*/ IF LEN (@f_string) > LEN (@f_delim)
AND @f_pos > 0
AND @f_pos <= LEN (@f_string)
--THEN
BEGIN
/*
||================================================================
|| Only look for a delimited string segment if:
|| 1. The delimiter is smaller than the actual string.
|| 2. The specified position within the string is a positive number
|| between 1 and the length of the string.
||
|| The 'high position' for the string segment is ascertained
|| below. If the delimiter is not found to the right of the
|| specified position, the high position is assigned as the last
|| byte of the string.
||================================================================
*/ IF @f_pos + 1 <= LEN (@f_string)
--THEN
BEGIN
SELECT @hipos = CHARINDEX (@f_delim, @f_string, @f_pos + 1)
IF @hipos = 0
--THEN
SELECT @hipos = LEN (@f_string)
ELSE
SELECT @hipos = @hipos - 1
--END IF
END
ELSE
SELECT @hipos = LEN (@f_string)
--END IF
/*
||================================================================
|| The 'low position' for the string segment is ascertained
|| below. There is no 'reverse CHARINDEX' function, so the string
|| itself is reversed and then adjusted for. If no occurrence
|| of the delimiter is found to the left of the specified position
|| within the string, then the low position is assigned to the
|| first byte of the string...
||================================================================
*/ IF @f_pos - 1 > 0
--THEN
BEGIN
SELECT @revdelim = REVERSE (@f_delim)
, @revstr = REVERSE (SUBSTRING (@f_string
, 1
, @f_pos - 1))
SELECT @lopos = CHARINDEX (@revdelim, @revstr)
IF @lopos = 0
--THEN
SELECT @lopos = 1
ELSE
SELECT @lopos = @f_pos - @lopos + 1
--END IF
END
ELSE
SELECT @lopos = 1
--END IF
/*
||================================================================
|| Now that the high and low boundaries of the string segment are
|| known, slice it out and return it...
||================================================================
*/ SELECT @segment = SUBSTRING (@f_string
, @lopos
, @hipos - @lopos + 1)
END
--END IF
RETURN @segment
END
GO 
