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:
ChangedFrom In Progress
ChangedTo Ready for Dispatch