sql-serversubstringudfpatindex

How to find and display all instances of a pattern in a SQL Server string?


So I am trying to write a UDF that will pull specific data from a string.

  'random text here Task 1234 blah blah more text task 4567'

I want to extract 'Task 1234 and task 4567' and have them display like such

  'Task 1234, task 4567'

Here's what I got so far, but only seem to be able to get either the first task or the second but not both.

Alter Function [dbo].[fn_GetTask](@strText VARCHAR(MAX))
RETURNS varchar(1000)
AS
BEGIN
  while patindex('%Task%', @strText) > 0

BEGIN
    SET @strText = substring(@strText, (CHARINDEX(substring(@strText, Patindex('%Task%', @strText) +4, 5), @strText, 5)),5)
    end
RETURN @strText

END

It's been a very long day. I feel like I'm missing something really basic here.


Solution

  • ALTER Function [dbo].[fn_GetTask](@strText VARCHAR(MAX))
    RETURNS varchar(1000)
    AS
    BEGIN
    
        DECLARE @ReturnString VARCHAR(1000) = ''
    
        WHILE PATINDEX('%Task%', @strText) > 0
    
        BEGIN
    
           DECLARE @FoundString VARCHAR(1000) = SUBSTRING(@strText,PATINDEX('%Task%',@strText),9)
    
           IF (LEN(@ReturnString) > 0)
           BEGIN
              SET @ReturnString += ', '
           END
    
           SET @ReturnString += @FoundString
    
           SET @strText = RIGHT(@strText,LEN(@strText) - PATINDEX('%' + @FoundString + '%',@StrText))
        END
    
        RETURN @ReturnString
    
    END
    

    As I had mentioned during your while loop you where over writing your string you where searching through with your results string. you didn't have a mechanism to trim that original string or track your progress here is one way to do it, I used additional variables just so I didn't have to nest the same statement a couple of times.