-1

Need some help with splitting a varchar field into multiple rows in oracle. example: in the given data sample, the order number is unique. I want to split the service code into multiple rows and each service code is separated by |

data sample

Thanks!

sivaranjani manoharan
  • 1
  • 2

1 Answers1

1

Here's an example.

Sample data is in lines #1 - 5 (you already have that in your table, don't type that); query that does the job begins at line #6.

SQL> with test (order_no, datum, service_cd) as
  2    (select 17, date '2016-11-30', '2106|2100|2105'      from dual union all
  3     select 23, date '2016-11-30', '2043|2020|2023|2047' from dual union all
  4     select 67, date '2016-11-30',  null                 from dual
  5    )
  6  select order_no,
  7         datum,
  8         regexp_substr(service_cd, '[^|]+', 1, column_value) val
  9  from test cross join
 10    table(cast(multiset(select level from dual
 11                        connect by level <= regexp_count(service_cd, '\|') + 1
 12                       ) as sys.odcinumberlist))
 13  order by order_no, datum, column_value;

  ORDER_NO DATUM      VAL
---------- ---------- ----------
        17 30/11/2016 2106
        17 30/11/2016 2100
        17 30/11/2016 2105
        23 30/11/2016 2043
        23 30/11/2016 2020
        23 30/11/2016 2023
        23 30/11/2016 2047
        67 30/11/2016

8 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank you. It works. A follow up question.. will this also work for word delimiters like 'AND'? – sivaranjani manoharan Jan 03 '21 at 21:32
  • You're welcome. AND, as a delimiter, should work. Though, beware of words that contain substring "and" as you'll get a *false positive*, i.e. a delimiter which actually isn't it. – Littlefoot Jan 03 '21 at 21:45