How to get specific words in an output of oracle query

Question

If I send a query like

select message_text
from x$dbgalert;

Then I get this result below..

ORA-1501 signalled during: CREATE DATABASE  "NEWDB"
          ,MAXINSTANCES 1
          ,MAXLOGHISTORY 1
          ,MAXLOGFILES 16

But I want to get a specific word which means 'ORA-1501' in the above result.

What I want is single or several special words that I want to see in long sentence that includes many words.

Answer 1

You could use SUBSTR and INSTR.

For example, let's say you have 3 rows in your table out of which only 2 rows have the ORA- error number.

SQL> WITH DATA(str) AS(
  2  SELECT 'ORA-1501 signalled during: CREATE DATABASE  "NEWDB"
  3            ,ORA- 1
  4            ,MAXLOGHISTORY 1
  5            ,MAXLOGFILES 16' FROM dual UNION ALL
  6  SELECT 'ORA-1999 signalled during: DROP DATABASE  "NEWDB"
  7            ,MAXINSTANCES 1
  8            ,MAXLOGHISTORY 1
  9            ,MAXLOGFILES 16' FROM dual UNION ALL
 10  SELECT 'signalled during: DROP DATABASE  "NEWDB"
 11            ,MAXINSTANCES 1
 12            ,MAXLOGHISTORY 1
 13            ,MAXLOGFILES 16' FROM dual
 14  )
 15  SELECT SUBSTR(str, 1, instr(str, ' ', 1, 1) -1) str
 16  FROM DATA
 17  WHERE instr(str, 'ORA-') > 0;

STR
----------------------------------------------------------------
ORA-1501
ORA-1999

SQL>

The WHERE instr(str, 'ORA-') > 0 i sto filter out those rows which do not have the ORA- error number.

Update The WITH clause is just to build a sample data for the demo. In your actual query you don't need the WITH clause.

You could simply use the query:

SELECT SUBSTR(message_text, 1, instr(message_text, ' ', 1, 1) -1) message_text
  FROM x$dbgalert
 WHERE instr(message_text, 'ORA-') > 0;