CODEWITHSUNDEEP
oracle-databaseplsql
user2854333
user2854333

Reputation: 640

Extract Data Between Brackets in Oracle SQL

I have a table with data in below format

COURSE
[]
["12345"]
["12345","7890"

I want to extract the data between [] but without "

So, my output would be in below format

COURSE
12345
12345, 7890

I tried the below code which works fine for first 3 rows

 select   REGEXP_SUBSTR (COURSE,
                     '"([^"]+)"',
                     1,
                     1,
                     NULL,
                     1) from TEST;

But 4th row only results in 12345.

Upvotes: 0

Views: 255

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Why not simple translate?

SQL> with test (course) as
  2    (select '[]'               from dual union
  3     select null               from dual union
  4     select '["12345"]'        from dual union
  5     select '["12345","7890"]' from dual
  6    )
  7  select course,
  8         translate(course, 'a[]"', 'a') result
  9  from test;

COURSE           RESULT
---------------- -----------------------------------------
["12345","7890"] 12345,7890
["12345"]        12345
[]


SQL>

Upvotes: 1

Related Questions