SQL Substring Extraction

I am trying to set two substring values, from an audit table, to respective columns but the solutions I've found pertain more to single character delimiters than substring delimiters. Any guidance and/or suggestions would be great as there are around 30 unique statuses and hundreds of 'from --> to' combinations of statuses.

What I have now (from the audit.audit_text field):

Ticket status has been changed from "In Progress" to "Ready for Dispatch".
Escalation level changed from "Plan" to "Waiting"

Here is what I've tried:

 SUBSTRING(dbo.SR_Audit.Audit_Text, CHARINDEX('to ', dbo.SR_Audit.Audit_Text) + 4, CHARINDEX('. ', dbo.SR_Audit.Audit_Text) - CHARINDEX('to',dbo.SR_Audit.Audit_Text) - 5) AS ChangedTo,

SUBSTRING(dbo.SR_Audit.Audit_Text, CHARINDEX('from ', dbo.SR_Audit.Audit_Text) + 6, CHARINDEX('to ',
                         dbo.SR_Audit.Audit_Text) - CHARINDEX('from', dbo.SR_Audit.Audit_Text) - 8) AS ChangedFrom

(See attached results)
The ChangedFrom field seems to be OK but the ChangedTo field has some unwanted results.
I want to strip the quote marks that surround each status and ignore everything after the first sentence.

Desired alias fields:

Alias                Value
ChangedFrom    In Progress
ChangedTo         Ready for Dispatch
Ben RapierAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Can't write any code against a picture of data.  Without actual INSERT statements with data, can only  guess at the code.
ste5anSenior DeveloperCommented:
When your audit text is well-formed as shown above:

      AuditText NVARCHAR(MAX)

VALUES  ( N'Ticket status has been changed from "In Progress" to "Ready for Dispatch".' ),
        ( N'Escalation level changed from "Plan" to "Waiting"' );

WITH    Data
          AS ( SELECT   S.AuditText ,
                        CAST(REPLACE(REPLACE(S.AuditText, ' "', '<s>'), '"', '</s>') AS XML) AS AuditTextXml
               FROM     @Sample S
    SELECT  D.AuditText ,
            FromStatus.value('.', 'NVARCHAR(255)') AS FromStatus ,
            ToStatus.value('.', 'NVARCHAR(255)') AS ToStatus
    FROM    Data D
            CROSS APPLY D.AuditTextXml.nodes('//s[1]') S1 ( FromStatus )
            CROSS APPLY D.AuditTextXml.nodes('//s[2]') S2 ( ToStatus );

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
I try to allow as many variances as possible.  I also push down the complex string manipulation to a step-by-step process in cross applys, to simply the main SELECT and to better debug.  For example:

select audit_text, ChangedFrom, ChangedTo
from ( --dbo.SR_Audit sa
    select 'Ticket status has been changed from "In Progress" to "Ready for Dispatch".' as audit_text union all
    select 'Escalation level changed from "Plan" to "Waiting"' UNION ALL
    select 'Whatever changed from "abc"def to "fgi,jkl.' UNION ALL
    select 'changed from "something" but that''s all no t=o string match' UNION ALL
    select 'changed from abc to xyz'
) as  sa --sample data
cross apply (
    select patindex('%[^a-z]to %',  sa.Audit_Text) as to_start
) as assign_alias_names1
cross apply (
    select patindex('%[^a-z]from %', sa.Audit_Text) as from_start
) as assign_alias_names2
cross apply (
    select case when from_start < to_start and to_start > 0 and from_start > 0 then 1 else 0 end as change_found
) as assign_alias_names3
cross apply (
    select (to_start - 1) - (from_start + 6) + 1 as from_length,
        charindex('.', sa.Audit_Text + '.', to_start + 5) - to_start - 4 as to_length
) as assign_alias_names4
cross apply (
        case when change_found = 0 then '' else substring(sa.Audit_Text, from_start + 6, from_length) end as changed_from_raw,
        case when change_found = 0 then '' else substring(sa.Audit_Text, to_start + 4, to_length) end as changed_to_raw
) as assign_alias_names5
cross apply (
        case when change_found = 0 then '' else
            case when left(changed_from_raw, 1) = '"' then '' else left(changed_from_raw, 1) end +
                substring(changed_from_raw, 2, len(changed_from_raw) - 1 -
                case when right(changed_from_raw, 1) = '"' then 1 else 0 end) end as ChangedFrom,
        case when change_found = 0 then '' else
            case when left(changed_to_raw, 1) = '"' then '' else left(changed_to_raw, 1) end +
                substring(changed_to_raw, 2, len(changed_to_raw) - 1 -
                case when right(changed_to_raw, 1) = '"' then 1 else 0 end) end as ChangedTo
) as assign_alias_names6
Ben RapierAuthor Commented:
Thanks for the quick answers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.