title: How to find and display all instances of a pattern in a SQL Server string?
body: <p>So I am trying to write a UDF that will pull specific data from a string. </p>
  
  <pre><code>  'random text here Task 1234 blah blah more text task 4567'
  </code></pre>
  
  <p>I want to extract 'Task 1234 and task 4567' and have them display like such</p>
  
  <pre><code>  'Task 1234, task 4567'
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>Alter Function [dbo].[fn_GetTask](@strText VARCHAR(MAX))
  RETURNS varchar(1000)
  AS
  BEGIN
    while patindex('%Task%', @strText) &gt; 0
  
  BEGIN
      SET @strText = substring(@strText, (CHARINDEX(substring(@strText, Patindex('%Task%', @strText) +4, 5), @strText, 5)),5)
      end
  RETURN @strText
  
  END
  </code></pre>
  
  <p>It's been a very long day. I feel like I'm missing something really basic here.  </p>