Generate rows from string of numbers

Question

by I have an Oracle 18c table that has strings like this:

select
    '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
    --There are more rows in the actual table.
from
    dual

    MULTIPART_LINES                                              
    -------------------------------------------------------------
    ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))
--      v1      v2           v3           v4           v5
--  |            part 1             | |          part 2         | 

• Individual coordinates are separated by spaces.
• Vertices (X Y Z coordinates) are separated by commas.
• Line parts are wrapped in brackets and separated by commas.

---

In a query, I want to generate rows for each vertex:

PART_NUM   VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54

• I want to do this in a query. I don't want to insert rows into a table.
• Unfortunately, I don't have CREATE TYPE privileges in the database. But I can create functions (and of course, inline functions are an option too).

---

How can I generate rows from the numbers (vertices) in the string?

Related: Oracle Ideas - generate_series() function

Answer 1

As an alternative - here is how you can process the input strings to convert them to proper JSON strings; then the task becomes trivial. Showing just the JSON-ization first, separately, as it really is the meaningful part of this solution; then after I show the query and result, I will complete the solution by adding the JSON manipulation.

with
  inputs (id, multipart_lines) as (
    select 2810,
      '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))'
                                  from dual union all
    select 7284, '((-2.3 0.2 3))' from dual
  )
, j (id, ml) as (
    select id,
           regexp_replace(
             regexp_replace(
               regexp_replace(
                 regexp_replace(
                   regexp_replace(multipart_lines
                   , '\(\s*\(\s*', '[[[')
                 , '\s*\)\s*\)', ']]]')
               , '\s*\)\s*,\s*\(\s*', '],[')
             , '\s*,\s*', '],[')
           , '\s+', ',')
    from   inputs
  )
select * from j;



   ID ML                                                                  
----- --------------------------------------------------------------------
 2810 [[[0,5,0],[10,10,11.18],[30,0,33.54]],[[50,10,33.54],[60,10,43.54]]]
 7284 [[[-2.3,0.2,3]]] 

Your inputs should really look like the strings in column ml in my subquery j - then you could process them like this:

with
  inputs (id, multipart_lines) as (
            ........
  )
, j (id, ml) as (
            ........
  )
select id, part_num, vertex_num, x, y, z
from   j,
       json_table(ml, '$[*]'
                  columns (
                    part_num for ordinality,
                    nested path '$[*]'
                    columns (
                      vertex_num for ordinality,
                      x number path '$[0]',
                      y number path '$[1]',
                      z number path '$[2]'
                    )
                  )
       )
order by id, part_num, vertex_num   --  if needed
;

The output is the same as in my other answer.