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.

MT0 · Accepted Answer · 2021-05-21 11:43:51Z

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

answered May 21, 2021 at 11:43

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

logi-kal · Accepted Answer · 2021-05-21 11:06:04Z

0

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>

answered May 21, 2021 at 11:06

logi-kal

7,8996 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

Need to extract data from string by regexp_substr

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related