将字符串oracle拆分为单个列并插入表中 [英] Split string oracle into a single column and insert into a table

问题描述

我有一个表格，其中的列数据采用以下格式(行号仅表示行号).

I have a table with column data in below format(Row# just to indicate row number).

Row#1 :test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236<br>
Row#2 :fil1.txt#delimiter#1456,fil1.txt#delimiter#1457

我想使用逗号(，)作为分隔符来分割字符串，将所有内容都列在一个单独的列中，然后插入到新表中.

I want to split the string using comma(,) as delimiter, list all in one single column and insert into a new table.

输出应该是这样的(行号只是表示行号):

output should be something like this (Row# just to indicate row number):

Row#1:test.doc#delimiter#1234<br>
Row#2:test1.doc#delimiter#1235<br>
Row#3:test2.doc#delimiter#1236<br>
Row#4: fil1.txt#delimiter#1456

有人可以帮我吗?

推荐答案

WITH data AS (
  SELECT 'test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236' AS "value" FROM DUAL
  UNION ALL
  SELECT 'fil1.txt#delimiter#1456,fil1.txt#delimiter#1457' AS "value" FROM DUAL
)
SELECT  REGEXP_SUBSTR( data."value", '[^,]+', 1, levels.COLUMN_VALUE )
FROM    data,
        TABLE(
          CAST(
            MULTISET(
              SELECT LEVEL
              FROM   DUAL
              CONNECT BY  LEVEL <= LENGTH( regexp_replace( "value", '[^,]+'))  + 1
            ) AS sys.OdciNumberList
          )
        ) levels;

这篇关于将字符串oracle拆分为单个列并插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！