Search code examples
sqljsonoracle-databasesplitoracle12c

How to replace special characters and then break line in oracle

This is how I am getting result in Oracle 12c

Id Date Range
1 [ "2019-01-07","2019-02-17","2019-03-17"]

And I want it

Id Date Range
1 2019-01-07
1 2019-02-17
1 2019-03-17

I tried replace function but it works either at the beginning or at end I want to remove [ ] and "

Solution

  • Since your Oracle version is 12, you can use JSON functions on your current output to get the desired output. Your current output is a valid JSON array of strings, all you need to do is to extract them. Something like this:

    with
  current_output (id, date_range) as (
    select 1, '["2019-01-07","2019-02-17","2019-03-17"]' from dual
  )
select co.id, t.date_range
from   current_output co
       cross apply
       json_table(co.date_range, '$[*]' columns date_range path '$') t
;

ID DATE_RANGE     
-- ---------------
 1 2019-01-07     
 1 2019-02-17     
 1 2019-03-17