Need to extract data from string by regexp_substr

Question

I have a ["1101124","1101123","123456"], I need to get the end result as rows for the numbers which are in the bracket. How can I achieve this by using regular expression in Oracle.

Answer 1

Don't use regular expression to try to parse JSON data; use a proper JSON parser:

SELECT value
FROM   JSON_TABLE(
         '["1101124","1101123","123456"]',
         '$[*]'
         COLUMNS(
           value VARCHAR2(20) PATH '$'
         )
       )

Outputs:

VALUE
1101124
1101123
123456

db<>fiddle here

Answer 2

If ["1101124","1101123","123456"] is a string:

SQL> WITH DATA AS
  2    ( SELECT '["1101124","1101123","123456"]' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[0-9]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY regexp_substr(str , '[0-9]+', 1, LEVEL) IS NOT NULL
  7  /

STR
----------------------------------------
1101124
1101123
123456

3 rows selected.

SQL>