Oracle 11gR2:使用多个定界符分割字符串(添加) [英] Oracle 11gR2: split string with multiple delimiters(add)

问题描述

String val = "ABC,abc|DEF,def|GHI,g hi|JKL,jkl";

如何使用sql，pl/sql或其他方法拆分此字符串并将其插入表中.

How to split this string and insert into tables using sql, pl/sql or whatever.

,是列定界符，|是行定界符.

, is column delimiter and | is row delimiter.

有可能吗?

[预期结果]

col1    col2
------------
ABC     abc
DEF     def
GHI     g hi
JKL     jkl

---

附加问题

感谢回复. 我还有一个问题.

---

Addition Question

Thanks reply. I have a another question.

这是一个字符串.

String val = "ABC,abc||D|@EF,def||G|HI,g hi||JKL,jkl";

我只想用||分隔符分割.如何使用RegExp?

I want to split by only || delimiter. how to use the RegExp?

我尝试了诸如'[^|]{2}+'，'^[|]{2}+', 1,等.

这是我的错误结果.

[错误结果]

COL1       COL2
---------- ----------
BC         abc
           D
@EF        def
           G
HI         g hi
JKL        jkl

[预期结果]

col1    col2
-----------
ABC     abc
D|@EF   def
G|HI    g hi
JKL     jkl

推荐答案

这是一种处理null且不存在更改任何数据的风险的不同方法.它使用with子句按定界符分解数据，最后以逗号分隔.

Here's a different approach that handles nulls and does not risk altering any data. It uses a with clause to break down the data by the delimiters, ending up with splitting on the commas.

假设:已采取步骤以确保已清除数据中的定界符(逗号和管道).

ASSUMPTION: Steps have already been taken to ensure that the data has already been scrubbed of the delimiter characters (commas and pipes).

-- Original data with multiple delimiters and a NULL element for testing.
with orig_data(str) as (
  select 'ABC,abc||||G|HI,g hi||JKL,jkl' from dual 
),
--Split on first delimiter (double-pipes)
Parsed_data(rec) as (
  select regexp_substr(str, '(.*?)(\|\||$)', 1, LEVEL, NULL, 1)
  from orig_data
  CONNECT BY LEVEL <= REGEXP_COUNT(str, '\|\|') + 1 
)
-- For testing-shows records based on 1st level delimiter
--select rec from parsed_data;

-- Split the record into columns
select regexp_replace(rec, '^(.*),.*', '\1') col1,
       regexp_replace(rec, '^.*,(.*)', '\1') col2
from Parsed_data;

这篇关于Oracle 11gR2:使用多个定界符分割字符串(添加)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！