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

Rate

Share

Share

Rate