Oracle Regular Expression to show string a string after a special character

Question

I am using regexp to get all the sub string after a special character ':' appear-

Given String-

Reason for creating the box: Order done by me (100005 - Error Format: Value ABC000001606 of EmpId is not valid)

Expected Output-

Order done by me (100005 - Error Format: Value ABC000001606 of EmpId is not valid)

I try this one but not working as required-

SELECT REGEXP_SUBSTR('Reason for creating the box: Order done by me (100005 - Error Format: Value ABC000001606 of EmpId is not valid)', ':[^ ])') "REGEXPR_SUBSTR" FROM DUAL;

Could you please help?

Answer 1

Here's two ways to do it.

This one uses REGEXP_SUBSTR, as you requested.

with c as (select 'Reason for creating the box: Order done by me (100005 - Error Format: Value ABC000001606 of EmpId is not valid)' str from dual)
SELECT substr(REGEXP_SUBSTR(c.str, ':[^)]+'),2) "REGEXPR_SUBSTR" FROM c;

This one uses substr+instr+length, which has much better performance if you have a large dataset.

with c as (select 'Reason for creating the box: Order done by me (100005 - Error Format: Value ABC000001606 of EmpId is not valid)' str from dual)
select substr(c.str, instr(c.str, ':')+1, length(c.str)-1)
from c;

Both of these have a WITH clause to make the example query more readable.