Using PATINDEX() In SQL Server As An Alternative To Embedd Regular Expressions In SQL

This was based on a post from SQL Team on how to grab a 5 digit number from the string field which could utilize a regular expression like this: [0-9 ]{5}. The initial solution was given by a forum member named Kristen. It could extract the 5 digit mark on a string field as shown below:

 DECLARE     @strTest varchar(8000)  
 SELECT @strTest = '\\servername\foldername99999\filename.ext'  
 SELECT [Position]=PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest),  
      [Before]=SUBSTRING(@strTest, 1, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest)-1),  
      [After]=SUBSTRING(@strTest, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @strTest), LEN(@strTest))  

What if the string field would contain two or more 5 digit information, how can you achieve that? The answer is to include while loop in TSQL and some additional string functions to achieve the desired output. Here's a sample stored procedure:

USE TESTDB;  
 GO  
 CREATE PROCEDURE spGetFiveDigits  
      @strTest1 varchar(500),  
      @strPattern varchar(200)  
 AS  
 DECLARE @strSearchPos int  
 SELECT @strSearchPos = 1  
 WHILE PATINDEX(@strPattern, @strTest1) != 0  
 BEGIN  
  SELECT  
      [Position]=PATINDEX(@strPattern, @strTest1),  
      [Before]=SUBSTRING(@strTest1, 1, PATINDEX(@strPattern, @strTest1) - 1),  
      [After]=SUBSTRING(@strTest1, PATINDEX(@strPattern, @strTest1), LEN(@strTest1)),  
      [Five_Digits]=SUBSTRING(@strTest1, PATINDEX(@strPattern, @strTest1), 5),  
      [Variable_Tested] = @strTest1;  
           set @strSearchPos = PATINDEX(@strPattern, @strTest1)  
           set @strTest1 = SUBSTRING(@strTest1, PATINDEX(@strPattern, @strTest1) + 5, LEN(@strTest1))  
 END  
 GO  

Running the stored procedure with two 5 digit string data:

EXEC spGetFiveDigits @strTest1 = N'12345\\servername\foldername\filename11111.ext',   
      @strPattern = N'%[0-9][0-9][0-9][0-9][0-9]%';  

Running the stored procedure with five 5 digit string data:

EXEC spGetFiveDigits @strTest1 = N'\\s12345ervername88888\folde54326rname99999\filename11111.ext',   
      @strPattern = N'%[0-9][0-9][0-9][0-9][0-9]%';