Skip to content
Advertisement

Capture string in between

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

Advertisement