How to get only dynamic value 0002970, 0007702 in with oracle query
Sample data:
~STTM_CUSTOMER~0007702~
~STTM_CUSTOMER~0002970~
>Solution :
Two simple & obvious options: regular expressions and substr + instr combination.
Sample data:
SQL> with test (key_id) as
2 (select '~STTM_CUSTOMER~0007702~' from dual union all
3 select '~STTM_CUSTOMER~0002970~' from dual
4 )
Query:
5 select
6 regexp_substr(key_id, '\d+') result_1,
7 --
8 substr(key_id, instr(key_id, '~', 1, 2) + 1,
9 instr(key_id, '~', 1, 3) - instr(key_id, '~', 1, 2) - 1
10 ) result_2
11 from test;
RESULT_1 RESULT_2
----------------------- -----------------------
0007702 0007702
0002970 0002970
SQL>