How to get only dynamic value 0002970, 0007702 in with oracle query

  Kiến thức lập trình

How to get only dynamic value 0002970, 0007702 in with oracle query

Sample data:
~STTM_CUSTOMER~0007702~
~STTM_CUSTOMER~0002970~
enter image description here

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>

LEAVE A COMMENT