Extract a particular keyword from string using regexp_substr in Oracle SQL

I have several strings in column Phone_mid_bsnl_AC_10035 and I want to extract the code next to AC that is 10035.

I used this SQL code:

WITH CTE(STR) AS 
(
    SELECT 'Phone_mid_bsnl_AC_10035' 
    FROM DUAL
)
SELECT REGEXP_SUBSTR(STR, '[^_]+', 1, 5) 
FROM CTE

It does give the code but it is not necessary in some of the strings that the code is present at the 5 position. Like in this string Phone_up_gsk_pdp_AC_90054 it is present in the 6 position. Is there a way to use the reg_exp to get only the digits after matching AC. The code will always be of 5 digits.

Use

SELECT REGEXP_SUBSTR(STR, '_(\d{5})(_|$)', 1, 1, null, 1) FROM CTE

instead. It will extract first occurence of 5 digits between _ and _ or end of the string.

Demo here.

WITH CTE(STR) AS (
SELECT 'Phone_mid_bsnl_A_C_10035' FROM DUAL union all
  SELECT 'Phone_mid_bsnl_AC_10035' FROM DUAL union all
  SELECT 'Phone_up_gsk_pdp_AC_90054' FROM DUAL union all
  SELECT 'Phone_mid_bsnl_A_C_10035_sad' FROM DUAL union all
  SELECT 'Phone_mid_bsnl_A_C_1003' FROM DUAL 
)
SELECT REGEXP_SUBSTR(STR, '_(\d{5})(_|$)', 1, 1, null, 1) a FROM CTE

It also can be done using regexp_replace

WITH CTE(STR) AS (
SELECT 'Phone_mid_bsnl_AC_10035' FROM DUAL union all
SELECT 'Phone_AC_10036_something' FROM DUAL
)
SELECT regexp_replace(STR, '.*AC_([0-9]{5}).*','') FROM CTE

Demo here

If numeric value is always at the end of a string, then you could simplify it to

sample data:

SQL> with test (col) as
  2    (select 'Phone_mid_bsnl_AC_10035' from dual)

Query:

  3  select substr(col, instr(col, '_', -1) + 1) result_1,
  4         regexp_substr(col, '\d+$') result_2
  5  from test;

RESULT_1   RESULT_2
---------- ----------
10035      10035

SQL>

• result_1: returns string that follows the last underline character
• result_2: returns numeric string "anchored" to the end of the source string