How can I capture data in between a series of strings?
I have the following data in a column: Basically, remove all before “INCIDENT TYPE:” and after “External ALARM ID:” “INCIDENT TYPE:” has no specific number of characters.
HOSTNAME: sample.com INCIDENT TYPE: SampleSiteUpDown SOURCED FROM External External ALARM ID: 57536
And I only want to display the incident type value
Incident Type SampleSiteUpDown SOURCED FROM External
Advertisement
Answer
Assuming your messages have the fixed format of:
HOSTNAME: ... INCIDENT TYPE: ... ALARM ID:
we can use the base string functions here:
SELECT SUBSTR(msg, INSTR(msg, 'INCIDENT TYPE:') + 15, INSTR(msg, 'ALARM ID:') - INSTR(msg, 'INCIDENT TYPE:') - 16) AS incident FROM yourTable;
Here is a working demo