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

Question

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.

Answer 1

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.