• 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)