How do I extract a repeating string pattern from multiple rows?
Here is a sample of how I would do this:

select 
      a.id
    , substring(a.value,T.N,7)
from 
    (
    values
        (1,'All of R489804 R489805 R489806 R489807 R489808'),   
        (2,'SP fr R308999                                 '),          
        (3,'R308777, R104621, R708931                     ')
    ) a(id,value)
cross apply (select N from Tally where N&lt;len(a.value)-5) T(N)
where substring(a.value,T.N,7) like 'R[0-9][0-9][0-9][0-9][0-9][0-9]';


Here, I am using a Tally (or numbers) table to walk the length of each value, extracting substrings of length 7 that match the required format.