CODEWITHSUNDEEP
sqloracle-database
Moudiz
Moudiz

Reputation: 7387

remove second quotes from text regexp_substs

I have the below text:

 PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier

I want to get the value PRODUCT_C, but I am getting TFSE instead

this is my query, usually the text wasora-00904 PRODUCT_C so the below was working. 

select  regexp_substr('PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier', '[^"]+', 1, 2) from dual

edit:

sometime my message will be like this

PL/SQL: ORA-00904: "PRODUCT_C": invalid identifier or like this

PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier

how can i adjust the regex_substr in a way to have result `

PRODUCT_C

Upvotes: 2

Views: 226

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31736

I would suggest you to first extract the third pattern separated by a colon and then search for the last pattern between the double quotes.

This adds another layer of check to ensure that you are picking the right element out of the error message before extracting the quoted string. 

SELECT
   regexp_substr( regexp_substr ( s, '[^:]+', 1, 3) , '"([^"]+)"$', 1, 1, NULL, 1) v 
FROM
   yourtable t

SQL Fiddle

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143133

Close, but no cigar, eh?

SQL> select  regexp_substr('PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier', '[^"]+', 1, 4) from dual;

REGEXP_SU
---------
PRODUCT_C

[EDIT, after reading the comment]

SQL> with test as (select 'PL/SQL: ORA-00904: "PRODUCT_C": invalid identifier' col from dual union
  2                select 'PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier' from dual
  3               )
  4  select col,
  5    regexp_substr(col, '[^"]+', 1, regexp_count(col, '"')) result
  6  from test;

COL                                                       RESULT
--------------------------------------------------------- ---------------
PL/SQL: ORA-00904: "PRODUCT_C": invalid identifier        PRODUCT_C
PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier PRODUCT_C

SQL>

Upvotes: 2

Related Questions