当前位置： 文江博客 话题详情

从数字字符串中生成行

发布于 2025-01-25 03:48:54 字数 1460 浏览 3 评论 0 原文

我有一个具有这样的字符串的Oracle 18C表：

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         |
  • 单个坐标被空间分开。
  • 顶点（XYZ坐标）通过逗号分隔。
  • 线零件用括号包裹，并用逗号分隔。

在查询中，我想为每个顶点生成行：

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
  • 我想在查询中执行此操作。我不想将行插入表中。
  • 不幸的是，我没有在数据库中创建类型特权。但是我 can 创建函数（当然，内联函数也是一个选项）。

如何从字符串中的数字（顶点）产生行？

相关： oracle构思 - generate_serate_serate_serate_series（generate_serate_series function ）

原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

碍人泪离人颜 2025-02-01 03:48:54

另外 - 您可以如何处理输入字符串将其转换为适当的JSON字符串；然后任务变得微不足道。首先仅显示JSON-IAGIAD，因为它确实是该解决方案的有意义的部分。然后，在显示查询和结果后，我将通过添加JSON操作来完成解决方案。

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]]]

您的输入确实应该看起来像我的子查询 J 中的列中的字符串 - 然后您可以这样处理它们：

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
;

输出与我的其他答案相同。

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.

回复 原文
与之呼应 2025-02-01 03:48:54

如果输入以某种标准格式（例如JSON），则将更简单。那么任务将是微不足道的。您对此有任何权力吗？

如果没有，您可以将输入转换为适当的JSON（或类似），也可以直接攻击问题。我说明了下面的后者，假设Oracle版本12.1或更高版本。

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
  )
select id, part_num, vertex_num, x, y, z
from   inputs 
       cross join lateral
       ( select  level as part_num,
                 regexp_substr(multipart_lines,
                               '\(([^()]+)\)', 1, level, null, 1) as part
         from    dual
         connect by level <= regexp_count(multipart_lines, '\(') - 1
       )
       cross join lateral
       (
         select  level as vertex_num,
                 regexp_substr(part, '[^,]+', 1, level) as vertex
         from    dual
         connect by level <= regexp_count(part, ',') + 1
       )
       cross join lateral
       (
         select   to_number(regexp_substr(vertex, '[^ ]+', 1, 1)) as x,
                  to_number(regexp_substr(vertex, '[^ ]+', 1, 2)) as y,
                  to_number(regexp_substr(vertex, '[^ ]+', 1, 3)) as z
         from     dual
       )
order by id, part_num, vertex_num   --  if needed
;

输出（来自我在查询中包含的样本输入）：

        ID   PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ---------- ----------
      2810          1          1          0          5          0
      2810          1          2         10         10      11.18
      2810          1          3         30          0      33.54
      2810          2          1         50         10      33.54
      2810          2          2         60         10      43.54
      7284          1          1       -2.3         .2          3

It would be so much simpler if the input was in some standard format - for example JSON. Then the task would be trivial. Do you have any power over that?

If not, you could either transform the input into proper JSON (or similar), or you could attack the problem directly. I illustrate the latter below, assuming Oracle version 12.1 or higher.

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
  )
select id, part_num, vertex_num, x, y, z
from   inputs 
       cross join lateral
       ( select  level as part_num,
                 regexp_substr(multipart_lines,
                               '\(([^()]+)\)', 1, level, null, 1) as part
         from    dual
         connect by level <= regexp_count(multipart_lines, '\(') - 1
       )
       cross join lateral
       (
         select  level as vertex_num,
                 regexp_substr(part, '[^,]+', 1, level) as vertex
         from    dual
         connect by level <= regexp_count(part, ',') + 1
       )
       cross join lateral
       (
         select   to_number(regexp_substr(vertex, '[^ ]+', 1, 1)) as x,
                  to_number(regexp_substr(vertex, '[^ ]+', 1, 2)) as y,
                  to_number(regexp_substr(vertex, '[^ ]+', 1, 3)) as z
         from     dual
       )
order by id, part_num, vertex_num   --  if needed
;

Output (from the sample inputs I included in the query):

        ID   PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ---------- ----------
      2810          1          1          0          5          0
      2810          1          2         10         10      11.18
      2810          1          3         30          0      33.54
      2810          2          1         50         10      33.54
      2810          2          2         60         10      43.54
      7284          1          1       -2.3         .2          3
回复 原文
酒绊 2025-02-01 03:48:54

我看到Mathguy到了那里。我正在使用JSON_TABLE 的工作，但是我无法一次解开2行，否则我基本上会在那里第二次使用row_number（）over（通过paru_num划分）。

 创建表示例（值VARCHAR（100））;
 
✓

 插入样本值 
（'（（0 5 0，10 10 11.18，30 0 33.54），（50 10 33.54，60 10 43.54））'）'）

1行影响

  AS（
选择'['|| regexp_replace（value，'\（| \）'，''）||'”]]
），
b as（（
从一个
），
c as（（
选择值c
来自json_table（（（从B中选择B），'$ [*]'
                列（值路径'

 x | y | z    
： -  | ： -  | ：------
0 | 5 | 0    
10 | 10 | 11.18
30 | 0 | 33.54
50 | 10 | 33.54
60 | 10 | 43.54

  AS（
选择'['|| regexp_replace（value，'\（+| \）+'，''''）||'”]
），
b as（（
从a中选择替换（a，'“”，'，'“”）b
），
c as（（
选择 
  row_number（）over（按'Zero'订购）pn，
  值c
来自json_table（（（从B中选择B），'$ [*]'
                列（值路径'

 | D |
| ：------------------------------------------------------- |
| [“ 1 0 5 0”，“ 1 10 10 11.18”，“ 1 30 0 33.54”] |
| [“ 2 50 10 33.54”，“ 2 60 10 43.54”] |



  AS（
选择'['|| regexp_replace（value，'\（+| \）+'，''''）||'”]
），
b as（（
从a中选择替换（a，'“”，'，'“”）b
），
c as（（
选择 
  row_number（）over（按'Zero'订购）pn，
  值c
来自json_table（（（从B中选择B），'$ [*]'
                列（值路径'

 ORA-01427：单行子查询返回多个行

db＆lt;＆gt;＆gt;

） ） ）， d as（（ 选择 光盘， 仪器（c，''）s1， 仪器（c，''，inst（c，''）+1）s2 来自C） 选择 substr（d，0，s1）x， substr（d，s1+1，s2-s1）y， substr（d，s2+1）z 从d 




















 db＆lt;＆gt;＆gt;

）
），
d as（（
选择'[“'|| pn ||' '||。
）
从D中选择 *
 












 db＆lt;＆gt;＆gt;

）
               ）
），
d as（（
选择
  光盘，
  仪器（c，''）s1，
  仪器（c，''，inst（c，''）+1）s2
来自C）
选择 
 substr（d，0，s1）x，
 substr（d，s1+1，s2-s1）y，
 substr（d，s2+1）z
从d
 
























 db＆lt;＆gt;＆gt;

）
），
d as（（
选择'[“'|| pn ||' '||。
），
e（
选择 
  row_number（）over（按'Zero'订购）pn，
  值c
来自json_table（（（从d中选择D），'$ [*]'
                列（值路径'




 db＆lt;＆gt;＆gt;

）
               ）
），
d as（（
选择
  光盘，
  仪器（c，''）s1，
  仪器（c，''，inst（c，''）+1）s2
来自C）
选择 
 substr（d，0，s1）x，
 substr（d，s1+1，s2-s1）y，
 substr（d，s2+1）z
从d
 
























 db＆lt;＆gt;＆gt;

）
），
d as（（
选择'[“'|| pn ||' '||。
）
从D中选择 *
 












 db＆lt;＆gt;＆gt;

）
               ）
），
d as（（
选择
  光盘，
  仪器（c，''）s1，
  仪器（c，''，inst（c，''）+1）s2
来自C）
选择 
 substr（d，0，s1）x，
 substr（d，s1+1，s2-s1）y，
 substr（d，s2+1）z
从d
 
























 db＆lt;＆gt;＆gt;

）
））
从E中选择 *
 




 db＆lt;＆gt;＆gt;

）
               ）
），
d as（（
选择
  光盘，
  仪器（c，''）s1，
  仪器（c，''，inst（c，''）+1）s2
来自C）
选择 
 substr（d，0，s1）x，
 substr（d，s1+1，s2-s1）y，
 substr（d，s2+1）z
从d
 
























 db＆lt;＆gt;＆gt;

）
），
d as（（
选择'[“'|| pn ||' '||。
）
从D中选择 *
 












 db＆lt;＆gt;＆gt;

）
               ）
），
d as（（
选择
  光盘，
  仪器（c，''）s1，
  仪器（c，''，inst（c，''）+1）s2
来自C）
选择 
 substr（d，0，s1）x，
 substr（d，s1+1，s2-s1）y，
 substr（d，s2+1）z
从d
 
























 db＆lt;＆gt;＆gt;



    
    

      
I see that mathguy has got there. I was working with from json_table but I can't unnest 2 rows at a time otherwise I'd be basically there with a second use of row_number() over (partition by Paru_num).




create table sample(value varchar(100));



✓







insert into sample values 
('((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))')





1 rows affected









with a as (
select '["'|| regexp_replace(value,'\(|\)','') ||'"]' a from sample
),
b as (
select regexp_replace(a,', ?','","') b from a
),
c as (
SELECT value c
FROM json_table( (select b from b) , '$[*]'
                COLUMNS (value PATH '





X   | Y   | Z    
:-- | :-- | :----
0   | 5   | 0    
10  | 10  | 11.18
30  | 0   | 33.54
50  | 10  | 33.54
60  | 10  | 43.54







with a as (
select '["'|| regexp_replace(value,'\(+|\)+','"') ||'"]' a from sample
),
b as(
select replace(a,'""','"')b from a
),
c as (
SELECT 
  row_number() over (order by 'zero') pn,
  value c
FROM json_table( (select b from b) , '$[*]'
                COLUMNS (value PATH '

| D                                          |
| :----------------------------------------- |
| ["1 0 5 0","1 10 10 11.18","1 30 0 33.54"] |
| ["2 50 10 33.54","2 60 10 43.54"]          |







with a as (
select '["'|| regexp_replace(value,'\(+|\)+','"') ||'"]' a from sample
),
b as(
select replace(a,'""','"')b from a
),
c as (
SELECT 
  row_number() over (order by 'zero') pn,
  value c
FROM json_table( (select b from b) , '$[*]'
                COLUMNS (value PATH '

ORA-01427: single-row subquery returns more than one row





db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here

)
)    ),
d as (
select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c
)
select * from d













db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here

)
)    ),
d as (
select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c
),
e as (
SELECT 
  row_number() over (order by 'zero') pn,
  value c
FROM json_table( (select d from d) , '$[*]'
                COLUMNS (value PATH '




db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here

)
)    ),
d as (
select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c
)
select * from d













db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here

)
)    )
select * from e





db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here

)
)    ),
d as (
select '["'|| pn ||' '|| regexp_replace(c,', ?','","'||pn||' ')||'"]' d from c
)
select * from d













db<>fiddle here

)
               )
),
d as (
SELECT
  c d,
  instr(c,' ') s1,
  instr(c,' ',instr(c,' ')+1) s2
from c)
select 
 substr(d,0,s1) x,
 substr(d,s1+1,s2-s1) y,
 substr(d,s2+1) z
from d

























db<>fiddle here


    

    
    
    
    

      
        
           回复
        
      

      
        
      

      

      
      

      

      
      
        原文 
      
      
    

  




        
        
        
        

  

    
      
    
  

  

    

      
        誰ツ都不明白
      
      
      2025-02-01 03:48:54
    

    
您只能使用简单的字符串函数（比正则表达式快得多）和递归子征出来来完成此操作：


WITH line_bounds (id, multipart_lines, line_no, spos, epos) AS (
    SELECT id,
           multipart_lines,
           1,
           2,
           INSTR(multipart_lines, ')', 2)
    FROM   table_name
UNION ALL
    SELECT id,
           multipart_lines,
           line_no + 1,
           epos + 2,
           INSTR(multipart_lines, ')', epos + 2)
    FROM   line_bounds
    WHERE  epos > 0
)
SEARCH DEPTH FIRST BY id SET line_order,
row_bounds (id, line, line_no, row_no, spos, epos) AS (
  SELECT id,
         SUBSTR(multipart_lines, spos + 1, epos - spos - 1),
         line_no,
         1,
         1,
         INSTR(
           SUBSTR(multipart_lines, spos + 1, epos - spos - 1),
           ',',
           1
         )
  FROM   line_bounds
  WHERE  epos > 0
UNION ALL
  SELECT id,
         line,
         line_no,
         row_no + 1,
         epos + 2,
         INSTR(line, ',', epos + 2)
  FROM   row_bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY id, line_no SET row_order,
column_bounds (id, line, line_no, row_no, xpos, ypos, zpos, epos) AS (
  SELECT id,
         line,
         line_no,
         row_no,
         spos,
         INSTR(line, ' ', spos, 1),
         INSTR(line, ' ', spos, 2),
         epos
  FROM   row_bounds
)
SELECT id,
       line_no,
       row_no,
       SUBSTR(line, xpos + 0, ypos - xpos) AS x,
       SUBSTR(line, ypos + 1, zpos - ypos - 1) AS y,
       CASE epos
       WHEN 0
       THEN SUBSTR(line, zpos + 1)
       ELSE SUBSTR(line, zpos + 1, epos - zpos - 1)
       END AS z
FROM   column_bounds



对于示例数据：输出：


CREATE TABLE table_name (id, multipart_lines) AS
  SELECT 1, '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' FROM DUAL UNION ALL
  SELECT 2, '((0 1 0, 0 2 0, 0 3 0),(0 4 0, 0 5 0))' FROM DUAL;



输出：








 id 
 line_no 
 row_no 
 x 
 y 
 z 






 1 
 1 
 1 
 0 
 5 
 0 




 1 
 1 
 2 
 10 
 10 
 11.18 




 1 
 1 
 3 
 30 
 0 
 33.54 




 1 
 2 
 1 
 50 
 10 
 33.54 




 1 
 2 
 2 
 60 
 10 
 43.54 




 2 
 1 
 1 
 0 
 1 
 0 




 2 
 1 
 2 
 0 
 2 
 0 




 2 
 1 
 3 
 0 
 3 
 0 




 2 
 2 
 1 
 0 
 4 
 0 




 2 
 2 
 2 
 0 
 5 
 0 








  db＆lt;＆gt; fiddle 此处 



    
    

      
You can do it with only simple string functions (which are much faster than regular expressions) and recursive sub-queries:


WITH line_bounds (id, multipart_lines, line_no, spos, epos) AS (
    SELECT id,
           multipart_lines,
           1,
           2,
           INSTR(multipart_lines, ')', 2)
    FROM   table_name
UNION ALL
    SELECT id,
           multipart_lines,
           line_no + 1,
           epos + 2,
           INSTR(multipart_lines, ')', epos + 2)
    FROM   line_bounds
    WHERE  epos > 0
)
SEARCH DEPTH FIRST BY id SET line_order,
row_bounds (id, line, line_no, row_no, spos, epos) AS (
  SELECT id,
         SUBSTR(multipart_lines, spos + 1, epos - spos - 1),
         line_no,
         1,
         1,
         INSTR(
           SUBSTR(multipart_lines, spos + 1, epos - spos - 1),
           ',',
           1
         )
  FROM   line_bounds
  WHERE  epos > 0
UNION ALL
  SELECT id,
         line,
         line_no,
         row_no + 1,
         epos + 2,
         INSTR(line, ',', epos + 2)
  FROM   row_bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY id, line_no SET row_order,
column_bounds (id, line, line_no, row_no, xpos, ypos, zpos, epos) AS (
  SELECT id,
         line,
         line_no,
         row_no,
         spos,
         INSTR(line, ' ', spos, 1),
         INSTR(line, ' ', spos, 2),
         epos
  FROM   row_bounds
)
SELECT id,
       line_no,
       row_no,
       SUBSTR(line, xpos + 0, ypos - xpos) AS x,
       SUBSTR(line, ypos + 1, zpos - ypos - 1) AS y,
       CASE epos
       WHEN 0
       THEN SUBSTR(line, zpos + 1)
       ELSE SUBSTR(line, zpos + 1, epos - zpos - 1)
       END AS z
FROM   column_bounds



Which, for the sample data:


CREATE TABLE table_name (id, multipart_lines) AS
  SELECT 1, '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' FROM DUAL UNION ALL
  SELECT 2, '((0 1 0, 0 2 0, 0 3 0),(0 4 0, 0 5 0))' FROM DUAL;



Outputs:








ID
LINE_NO
ROW_NO
X
Y
Z






1
1
1
0
5
0




1
1
2
10
10
11.18




1
1
3
30
0
33.54




1
2
1
50
10
33.54




1
2
2
60
10
43.54




2
1
1
0
1
0




2
1
2
0
2
0




2
1
3
0
3
0




2
2
1
0
4
0




2
2
2
0
5
0








db<>fiddle here


    

    
    
    
    

      
        
           回复
        
      

      
        
      

      

      
      

      

      
      
        原文 
      
      
    

  




        
        
        
        

  

    
      
    
  

  

    

      
        无法回应
      
      
      2025-02-01 03:48:54
    

    
使用 sdo_geometry 来解析字符串，然后使用 sdo_util.extract 获取每个元素，然后 sdo_util.getvertices 获取顶点：


SELECT t.id,
       e.elem_no,
       v.id AS coord_id,
       x,
       y,
       z
FROM   ( SELECT id,
                SDO_GEOMETRY('MULTILINESTRING '||multipart_lines) AS shape
         FROM   table_name
       ) t
       CROSS JOIN LATERAL (
         SELECT LEVEL AS elem_no,
                SDO_UTIL.EXTRACT(t.shape, LEVEL) AS elem
         FROM   DUAL
         CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(t.shape)
       ) e
       CROSS APPLY TABLE(SDO_UTIL.GETVERTICES(e.elem)) v



对于哪个元素：示例数据：


CREATE TABLE table_name (id, multipart_lines) AS
  SELECT 1, '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' FROM DUAL UNION ALL
  SELECT 2, '((0 1 0, 0 2 0, 0 3 0),(0 4 0, 0 5 0))' FROM DUAL;



输出：








 id 
 elem_no 
 coord_id 
 x 
 y 
 z 






 1 
 1 
 1 
 0 
 5 
 0 




 1 
 1 
 2 
 10 
 10 
 11.18 




 1 
 1 
 3 
 30 
 0 
 33.54 




 1 
 2 
 1 
 50 
 10 
 33.54 




 1 
 2 
 2 
 60 
 10 
 43.54 




 2 
 1 
 1 
 0 
 1 
 0 




 2 
 1 
 2 
 0 
 2 
 0 




 2 
 1 
 3 
 0 
 3 
 0 




 2 
 2 
 1 
 0 
 4 
 0 




 2 
 2 
 2 
 0 
 5 
 0 








  db＆lt;＆gt; fiddle 此处 



    
    

      
Use SDO_GEOMETRY to parse the string and then use SDO_UTIL.EXTRACT to get each element and then SDO_UTIL.GETVERTICES to get the vertices:


SELECT t.id,
       e.elem_no,
       v.id AS coord_id,
       x,
       y,
       z
FROM   ( SELECT id,
                SDO_GEOMETRY('MULTILINESTRING '||multipart_lines) AS shape
         FROM   table_name
       ) t
       CROSS JOIN LATERAL (
         SELECT LEVEL AS elem_no,
                SDO_UTIL.EXTRACT(t.shape, LEVEL) AS elem
         FROM   DUAL
         CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(t.shape)
       ) e
       CROSS APPLY TABLE(SDO_UTIL.GETVERTICES(e.elem)) v



Which, for the sample data:


CREATE TABLE table_name (id, multipart_lines) AS
  SELECT 1, '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' FROM DUAL UNION ALL
  SELECT 2, '((0 1 0, 0 2 0, 0 3 0),(0 4 0, 0 5 0))' FROM DUAL;



Outputs:








ID
ELEM_NO
COORD_ID
X
Y
Z






1
1
1
0
5
0




1
1
2
10
10
11.18




1
1
3
30
0
33.54




1
2
1
50
10
33.54




1
2
2
60
10
43.54




2
1
1
0
1
0




2
1
2
0
2
0




2
1
3
0
3
0




2
2
1
0
4
0




2
2
2
0
5
0








db<>fiddle here


    

    
    
    
    

      
        
           回复
        
      

      
        
      

      

      
      

      

      
      
        原文 
      
      
    

  




        
        
        
        

  

    
      
    
  

  

    

      
        心不设防
      
      
      2025-02-01 03:48:54
    

    
@solomonyakobson在


with sample as (
                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
               )
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  sample,
        lateral(
                select  level part_num,
                        regexp_substr(multipart_lines,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(multipart_lines,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )
/

  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





以及Oracle 19c解决方案：


create or replace
  function split_multipart_line(
                                p_line varchar2
                               )
    return varchar2
    sql_macro
    is
    begin
        return q'[
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  dual,
        lateral(
                select  level part_num,
                        regexp_substr(p_line,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(p_line,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )]';
end;
/

Function created.

SQL> with sample as (
  2                  select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
  3                  select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
  4                 )
  5  select  l.*
  6    from  sample,
  7          lateral(
  8                  select  *
  9                    from  split_multipart_line(multipart_lines)
 10                 ) l
 11  /


  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
         1          1          1          2          3
         1          2          4          5          6
         1          3          7          8          9
         1          4         10         11         12
         2          1         22         33         44
         2          2         55         66         77


11 rows selected.

SQL>




    
    

      
@SolomonYakobson provided this answer in an Oracle Community post.


with sample as (
                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
               )
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  sample,
        lateral(
                select  level part_num,
                        regexp_substr(multipart_lines,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(multipart_lines,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )
/

  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





As well as an Oracle 19c solution:


create or replace
  function split_multipart_line(
                                p_line varchar2
                               )
    return varchar2
    sql_macro
    is
    begin
        return q'[
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  dual,
        lateral(
                select  level part_num,
                        regexp_substr(p_line,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(p_line,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )]';
end;
/

Function created.

SQL> with sample as (
  2                  select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
  3                  select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
  4                 )
  5  select  l.*
  6    from  sample,
  7          lateral(
  8                  select  *
  9                    from  split_multipart_line(multipart_lines)
 10                 ) l
 11  /


  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
         1          1          1          2          3
         1          2          4          5          6
         1          3          7          8          9
         1          4         10         11         12
         2          1         22         33         44
         2          2         55         66         77


11 rows selected.

SQL>



    

    
    
    
    

      
        
           回复
        
      

      
        
      

      

      
      

      

      
      
        原文 
      
      
    

  




        
        
        
~没有更多了~

      

    

    
  


  
  
  



            
            
          

          

  

  
  

  
  
  

    

      

        
关于作者

      

      

        
          
        
        鹊巢
        
暂无简介

      

      

        

          
          文章
        

        

          
          评论
        

        

          27
          人气
        

      

      

        
        
           发私信
        
      

    

  

  

  
  

  
  

  
  
  




  

    
  


  
  

    

      

        
相关话题

      

      

        
      

    

  

  
  

  
  

    

      

        更多 
        
热门标签

      

      

        
        操作系统
        
        程序设计
        
        IT运维
        
        Linux系统管理
        
        JavaScript
        
        服务器应用
        
        solaris
        
        C/C++
        
        PHP
        
        Shell
        
        BSD
        
        Vue.js
        
        aix
        
        Oracle
        
        Python
        
        HTML
        
        系统管理
        
        HTML5
        
        CSS
        
        前端
        
      

    

  

  

  

  
  

    

      

        更多 
        
推荐作者

      

      

        

          
          

            

              
                
              
            

            
            

              
十二

              

                文章 0
                评论 0
              

            

          

          
          

            

              
                
              
            

            
            

              
飞烟轻若梦

              

                文章 0
                评论 0
              

            

          

          
          

            

              
                
              
            

            
            

              
OPleyuhuo

              

                文章 0
                评论 0
              

            

          

          
          

            

              
                
              
            

            
            

              
wxb0109

              

                文章 0
                评论 0
              

            

          

          
          

            

              
                
              
            

            
            

              
旧城空念

              

                文章 0
                评论 0
              

            

          

          
          

            

              
                
              
            

            
            

              
-小熊_

              

                文章 0
                评论 0
              

            

          

          
        

      

    

  

  

  
  

  

    

      

        更多 
        
友情链接

      

      

        
        文江博客
        
      

    

  


  

    

      

        

          
    
        
    
      
    
    
    
  
    

    

        
    
      
    
    
    

    


    
  
    
    
    
    
    
  
    

    


    
  
    
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的
    隐私政策
    了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
  
    
  
    
    
    
  
    

    


    
  
    
    原文