Parse userid from a text field to a new column

Currently this is what I have and I would want to parse out the userid and add it to the line below it (and before the next line that contains another userid) as a new field.

The result would look like this. Any idea how this would be accomplished in SQl Server?

Answer

Hmmm . . . One method is to use a conditional window function to get the preceding row with '##User: '. Then join back to that row to get the user:

select t.*,
       stuff(tu.notes, 1, 8, '') as user
from (select t.*,
             max(case when notes like '##User: %' then lineno end) over (partition by orderno order by lineno) as user_lineno
      from t
     ) t left join
     t tu
     on tu.lineno = t.user_lineno
where notes not like '##User: %';