Alternate T-SQL Approach for Creating a PATINDEX Funtion to Find Multiple Occurrences of a Pattern

Question

I'm working on a project to parse through numerous varchar max fields for multiple occurrences of specific patterns. For example, a cemetery plot was originally sold on 2/14/1928, then deeded on 7/4/1975 and deeded again on 12/31/2010. PATINDEX works great for finding the first instance of a pattern but obviously doesn't work for finding multiple occurrences. To get around this, I wrote the stored procedure below and it works well for identifying multiple instances of a pattern. I was hoping to create it as a function but I discovered that executing SQL statements in a function is a no go. I'm having a difficult time thinking of different ways I could rewrite this so it would work as a function and I'm hoping some of you T-SQL rockstars may be able to offer some suggestions. Thank you in advance for any help you can offer!

Executing the following statement identifies the third occurrence of a lowercase letter next to a number and correctly returns 10 as the starting position of the occurrence.

EXEC dbo.PATINDEX2 @TargetStr = '%[a-z][0-9]%', @SearchedStr = 'abc1acc2ba6d', @Occurrence = 3

This is the stored procedure:

CREATE PROCEDURE [dbo].[PATINDEX2] (@TargetStr VARCHAR(MAX),@SearchedStr VARCHAR(MAX),@Occurrence INT) AS
  BEGIN
      DECLARE @TSQL          VARCHAR(MAX),
              @TempSearchStr VARCHAR(MAX),
              @Position      INT,
              @Counter       INT,
              @ReturnValue   INT,
              @PositionSum   INT = 0
      DECLARE @TempCharPos TABLE
        (
           [ID]    INT IDENTITY(1, 1),
           CharPos INT
        )

      SET @TSQL = 'SELECT PATINDEX(''' + @TargetStr + ''', ''' + @SearchedStr + ''')'

      INSERT INTO @TempCharPos
      EXEC(@TSQL)

      SET @Position = (SELECT CharPos
                       FROM   @TempCharPos
                       WHERE  [ID] IN (SELECT MAX([ID])
                                       FROM   @TempCharPos))
      SET @Counter = 1
      SET @PositionSum = @PositionSum + @Position

      IF @Occurrence = 1
        SET @ReturnValue = @Position
      ELSE
        BEGIN
            WHILE ( @Counter < @Occurrence )
              BEGIN
                  SET @TempSearchStr = SUBSTRING(@SearchedStr, @PositionSum + 1, LEN(@SearchedStr) + 1)
                  SET @TSQL = 'SELECT PATINDEX(''' + @TargetStr + ''', ''' + @TempSearchStr + ''')'
                  INSERT INTO @TempCharPos
                  EXEC(@TSQL)

                  SET @Position = (SELECT CharPos
                                   FROM   @TempCharPos
                                   WHERE  [ID] IN (SELECT MAX([ID])
                                                   FROM   @TempCharPos))
                  SET @PositionSum = @PositionSum + @Position
                  SET @ReturnValue = @PositionSum
                  SET @Counter = @Counter + 1
              END
        END
      SELECT ( @ReturnValue )
  END
GO

Answer 1

Performance considerations and other alternatives such as CLR aside, maybe something like this?:-

CREATE FUNCTION dbo.fcnPATINDEX2 (
    @TargetStr NVARCHAR(MAX),
    @SearchedStr VARCHAR(MAX),
    @Occurrence INT
    )
RETURNS INT
AS
BEGIN
    DECLARE @Position INT;
    DECLARE @ReturnValue INT;
    DECLARE @Counter INT;

    SET @Position = 0;
    SET @ReturnValue = 0;
    SET @Counter = 1;

    /* Check we have some good inputs */
    IF LEN(@TargetStr) > 0
        AND LEN(@SearchedStr) > 0
        AND @Occurrence > 0
    BEGIN
        /* Loop the number of required occurrences */
        WHILE @Counter <= @Occurrence
        BEGIN
            /* Increment the loop counter */
            SET @Counter = @Counter + 1;

            /* See if the search pattern exists in the text to be searched */
            SET @Position = PATINDEX(@TargetStr COLLATE Latin1_General_100_BIN2, 
                                     @SearchedStr COLLATE Latin1_General_100_BIN2);

            /* If the search pattern exists */
            IF @Position > 0
            BEGIN
                /* Remove the text preceding the search pattern */
                /* (including the first character of the search pattern) */
                /* from the text to be searched in the next iteration */
                SET @SearchedStr = SUBSTRING(@SearchedStr, @Position + 1, LEN(@SearchedStr));

                /* Keep a running total of the position of the search pattern matches */
                SET @ReturnValue = @ReturnValue + @Position;
            END
            ELSE
            /* The search pattern does not exist */
            /* or the search pattern does not exist for the requested occurrence */
            BEGIN
                SET @ReturnValue = 0;

                BREAK;
            END;
        END;
    END;

    RETURN @ReturnValue;
END
GO

Testing on a case-sensitive database (Latin1_General_CS_AS)

SELECT dbo.fcnPATINDEX2('%[a-z][0-9]%', 'abc1acc2ba6d', 1); /* 3 */
SELECT dbo.fcnPATINDEX2('%[a-z][0-9]%', 'abc1acc2ba6d', 2); /* 7 */
SELECT dbo.fcnPATINDEX2('%[a-z][0-9]%', 'abc1acc2ba6d', 3); /* 10 (your example) */
SELECT dbo.fcnPATINDEX2('%[a-z][0-9]%', 'abc1acc2ba6d', 4); /* 0 */
SELECT dbo.fcnPATINDEX2('%[a-z][0-9]%', 'ABC1ACC2BA6D', 3); /* 0 */
SELECT dbo.fcnPATINDEX2('%[A-Z][0-9]%', 'abc1acc2ba6d', 3); /* 0 */
SELECT dbo.fcnPATINDEX2('%[A-Z][0-9]%', 'ABC1ACC2BA6D', 3); /* 10 (your example in uppercase) */