也可以使用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}).*','\1') FROM CTE

Demo here

使用

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

它将提取_和_之间的第一个5位数或字符串的结尾。
演示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

如果numeric value总是在字符串的末尾，那么可以将其简化为
样本数据：

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

查询：

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：返回最后一个下划线字符后的字符串
• result_2：返回“锚定”到源字符串末尾的数字字符串