Capture string in between-CodePudding

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

CodePudding user response：

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

CodePudding user response：

One option is to use good, old substr instr combination:

SQL> with test (col) as
  2    (select 'HOSTNAME: sample.com INCIDENT TYPE: SampleSiteUpDown SOURCED FROM External External ALARM ID: 57536'
  3     from dual
  4    )
  5  select substr(col, instr(col, 'INCIDENT TYPE:')   15,
  6                     instr(col, 'External ALARM ID') - instr(col, 'INCIDENT TYPE:') - 15
  7               ) result
  8  from test;

RESULT
---------------------------------------
SampleSiteUpDown SOURCED FROM External

SQL>

CodePudding user response：

Have a look at REGEXP_ SUBSTR queries, using something like

SELECT REGEXP_SUBSTR(msg, 'INCIDENT TYPE: (.* SOURCED FROM External)', 1, 1,'', 1) FROM ...

Demo

It's a little more concise and a little more flexible than the other solutions, I think.