If the audit strings won't ever be longer than 8K, here's one way. It's quite generic so it won't necessarily be the fastest method but will still perform quite well and is quite easy if you understand what a Tally Table does.
First, create the following function...
CREATE FUNCTION dbo.TokenizeAuditString
(@pString VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
--========== Generate up to 10K rows ("En" indicates the power of 10 produced)
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))n(N)),
E4(N) AS (SELECT 1 FROM E1 a,E1 b,E1 c, E1 d),
cteTally(N) AS (SELECT TOP (LEN(@pString)) ROW_NUMBER()OVER(ORDER BY N) FROM E4),
cteSplit AS
( --======== Split the string on the delimiters of '< and >'
SELECT FieldNumber = ROW_NUMBER() OVER (ORDER BY t.N)
,FieldString = SUBSTRING(@pString, (t.N+2) ,CHARINDEX('>''',@pString,t.N)-(t.N+2))
FROM cteTally t
WHERE t.N <= LEN(@pString)
AND SUBSTRING(@pString,t.N,2) = '''<'
) --======== Repivot the string elements back to a horizontal row using a CrossTab.
SELECT FieldName = MAX(CASE WHEN FieldNumber = 1 THEN FieldString ELSE '' END)
,ChangedFrom = MAX(CASE WHEN FieldNumber = 2 THEN FieldString ELSE '' END)
,ChangedTo = MAX(CASE WHEN FieldNumber = 3 THEN FieldString ELSE '' END)
,ChangedBy = MAX(CASE WHEN FieldNumber = 4 THEN FieldString ELSE '' END)
,ChangedDate = MAX(CASE WHEN FieldNumber = 5 THEN FieldString ELSE '' END)
FROM cteSplit
;
Then, call it to "normalize" your entire table like this...
SELECT split.FieldName
,split.ChangedFrom
,split.ChangedTo
,split.ChangedBy
,split.ChangedDate
FROM dbo.YourTable yt
CROSS APPLY dbo.TokenizeAuditString(yt.TheColumn)split
;
--Jeff Moden
Change is inevitable... Change for the better is not.