Seeking all Chevrons <%> 1

[Unravel]

System is MS SQL SERVER 2008 R2.
I have a field in a table called Clinical notes. The data types is longdesctype(text). Within this field is kept running notes from every user's data entry. Our system (Trizetto) logs each unique entry with the date and an 8 digit username between chevrons.
For example <04/29/2014 08:51 - GunderKa> OR <04/21/2014 08:18 - SullivaJ> Between these entries are notes typed in paragraph style:

For example:
<04/29/2014 08:51 - BallLuci>
Patient was admitted for possible apendectomy on night of 4/28 with normal BP.
She states she ate too many hot dogs at the festival and felt a sharp pain in her abdomen
Suggest exploritory surgery to rule out aliens.
<04/30/2014 08:18 - SullivaJ>
Inpatient stay approved for 2 nights, waterbed OK. Dr FeelGood to attend.
<04/30/2014 20:18 - BennyJac>
Post surgery patient is stable and wants a BigMac.

I want to capture every instance of <Date & Name> .... without the intermittent notes.
From the back end, without a GUI, the field is one very long paragraph. How do I capture only the chevrons and the text within the chevrons?

 

[bborissov]

Based on:

https://ask.sqlservercentral.com/questions/19855/find-substring-saved-between-2-characters.html

DECLARE @string varchar(max)
SET @string =
 '<04/29/2014 08:51 - BallLuci>
Patient was admitted for possible apendectomy on night of 4/28 with normal BP.
She states she ate too many hot dogs at the festival and felt a sharp pain in her abdomen
Suggest exploritory surgery to rule out aliens.
<04/30/2014 08:18 - SullivaJ>
Inpatient stay approved for 2 nights, waterbed OK. Dr FeelGood to attend.
<04/30/2014 20:18 - BennyJac>
Post surgery patient is stable and wants a BigMac.'

DECLARE @TestTable TABLE (Fld1 varchar(max))
INSERT INTO @TestTable VALUES (@string)
SET @string =
 '<04/29/2014 08:51 - BallLuci111111>
Patient was admitted for possible apendectomy on night of 4/28 with normal BP.
She states she ate too many hot dogs at the festival and felt a sharp pain in her abdomen
Suggest exploritory surgery to rule out aliens.
<04/30/2014 08:18 - SullivaJ22222>
Inpatient stay approved for 2 nights, waterbed OK. Dr FeelGood to attend.
<04/30/2014 20:18 - BennyJac333333>
Post surgery patient is stable and wants a BigMac.'

INSERT INTO @TestTable VALUES (@string)



DECLARE @Testme TABLE (I int, N int IDENTITY (1,1))
INSERT INTO @Testme (I)
SELECT TOP 100000 1
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2


SELECT *
FROM (select substring(Tst2.Fld1, tst1.N, charindex('>', Tst2.Fld1, tst1.N)-tst1.N+1) AS Test
            from @Testme tst1
      CROSS JOIN @TestTable Tst2
      where tst1.N <= len(Tst2.Fld1)
    and SUBSTRING(@string,tst1.N,1) = '<') TTT

Borislav Borissov
VFP9 SP2, SQL Server