split ssrs expression between 2 words

Question

I have a requirement to split a result up into 3 separate expressions, Using sql server 2008.

The result i currently get is for example. "person: man tel: yes rel: christian msg: misc text"

I need it split into:

"Person: man" "Tel: yes" "Rel: Christian" "Msg: misc text"

I have been using an iif statement to return the bit before "tel" but cant get it to return only between tel and rel etc. The part before the colon will remain static whichever result is returned in my dataset, but the part after the colon could be anything (of any length too) before getting to the next section. Can anyone help please? The other similar questions on the forum have been helpful with the first expression, but the later ones are a little more difficult.

Answer 1

For a SQL method this works for the test I did:

SELECT LEFT(txt, CHARINDEX('tel:',txt)-2) AS part1,
    SUBSTRING(txt, CHARINDEX('tel:',txt), CHARINDEX('rel:',txt)-CHARINDEX('tel:',txt)) AS part2,
    SUBSTRING(txt, CHARINDEX('rel:',txt), CHARINDEX('msg:',txt)-CHARINDEX('rel:',txt)) AS part3,
    RIGHT(txt, LEN(txt)-CHARINDEX('msg:',txt)+1) AS part4
FROM (
    SELECT 'person: man tel: yes rel: christian msg: misc text' AS txt
) AS t