1

sample column data:

Failure on table TOLL_USR_TRXN_HISTORY:
Failure on table DOCUMENT_IMAGES:
Error in CREATE_ACC_STATEMENT() [line 16]

I am looking for a way to extract only the uppercase words (table names) separated by underscores. I want the whole table name, the maximum is 3 underscores and the minimum is 1 underscore. I would like to ignore any capital letters that are initcap.

Sibusiso Maphumulo
  • 13
  • 4

3 Answers3

1

You can just use regexp_substr():

select regexp_substr(str, '[A-Z_]{3,}', 1, 1, 'c')
from (select 'Failure on table TOLL_USR_TRXN_HISTORY' as str from dual) x;

The pattern says to find substrings with capital letters or underscores, at least 3 characters long. The 1, 1 means start from the first position and return the first match. The 'c' makes the search case-sensitive.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You may use such a SQL Select statement for each substituted individual line ( Failure on table TOLL_USR_TRXN_HISTORY in the below case ) from your text :

select regexp_replace(q.word, '[^a-zA-Z0-9_]+', '') as word 
  from
(
  select substr(str,nvl(lag(spc) over (order by lvl),1)+1*sign(lvl-1),
         abs(decode(spc,0,length(str),spc)-nvl(lag(spc) over (order by lvl),1))) word, 
         nvl(lag(spc) over (order by lvl),1) lg
    from
  (
    with tab as
      ( select 'Failure on table TOLL_USR_TRXN_HISTORY' str from dual )
        select instr(str,' ',1,level) spc, str, level lvl
          from tab 
       connect by level <= 10
  )
) q
where lg > 0
    and upper(regexp_replace(q.word, '[^a-zA-Z0-9_]+', '')) 
      = regexp_replace(q.word, '[^a-zA-Z0-9_]+', '')
  and ( nvl(length(regexp_substr(q.word,'_',1,1)),0) 
      + nvl(length(regexp_substr(q.word,'_',1,2)),0) 
      + nvl(length(regexp_substr(q.word,'_',1,3)),0)) > 0
  and   nvl(length(regexp_substr(q.word,'_',1,4)),0)  = 0;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

Alternate way to get only table name from below error message , the below query will work only if table_name at end in the mentioned way

with t as( select 'Failure on table TOLL_USR_TRXN_HISTORY:' as data from dual)
SELECT RTRIM(substr(data,instr(data,' ',-1)+1),':') from t

New Query for all messages : 

 select  replace (replace ( 'Failure on table TOLL_USR_TRXN_HISTORY:
Failure on table DOCUMENT_IMAGES:' , 'Failure on table', ' ' ),':',' ') from dual
kanagaraj
  • 442
  • 1
  • 3
  • 8
  • what if string is 'Failure on table TOLL_USR_TRXN_HISTORY TOLL_USR_TRXN_HISTORY2:' ..? Then, returns `TOLL_USR_TRXN_HISTORY2` only. And there's no filter for all uppercase letters and underscores. And returns only the last word of the sentence. – Barbaros Özhan Sep 07 '18 at 18:02
  • 1
    Yes Barbaro ... it wont work . This will only pick last word – kanagaraj Sep 07 '18 at 18:10
  • I have updated query to handle mutilple messages ...since error messages are common I went with this query .... but I see your query was awesome whatever it can be :-) – kanagaraj Sep 07 '18 at 18:26