将列文本拆分为行（在括号中提取定界符）ORACLE SQL

本文介绍了将列文本拆分为行（在括号中提取定界符）ORACLE SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在数据库中有一列，其中一列包含多个值，我需要将其作为不同的行。
列包含以逗号分隔的部分，但也包含带逗号的部分。我不需要拆分这部分。（仅对不放在方括号中的逗号进行分隔）

I have a Column in a Database which contains multiple Values in one Column, which i need as different rows.The Column contains comma delimited parts but also a Part with comma in brackets. I don't need to split this parts. (Only split on commas which are NOT in brackets)

Oracle 11g

**ID | Kategory**

1 | "ATD 5(2830),ATO 4(510),EDI 1,EH A1,SCI 2,SS 1,STO-SE 1(oral, CNS, blood),STO-SE 2(oral, respiratory effects)"

我需要的字符串

 - 1 => ATD 5(2830)
 - 1 => ATO 4(510)
 - 1 => EDI 1
 - 1 => EH A1
 - 1 => SCI 2
 - 1 => SS 1
 - 1 => STO-SE 1(oral,CNS, blood)
 - 1 => STO-SE 2(oral, respiratory effects)

零件（如口腔，中枢神经系统，血液）在括号中包含逗号i

Parts like (oral, CNS, blood) which contains comma in brackets i don't need to split.

推荐答案

您可以使用正则表达式（（[[^（] *？（\（。*？\））？）*）（，| $）匹配：

You can use the regular expression (([^(]*?($.*?$)?)*)(,|$) to match:

[^（] *？零个或多个（但应尽可能少）非开括号字符

（\（。*？\））？然后，可选地，使用一个大括号和尽可能少的字符，直到大括号为止。

（）* 包裹在一个捕获组中，重复零次或多次

（）包裹在捕获组中，以便能够引用整个匹配项

（，| $）后跟逗号或字符串结尾。

[^(]*? Zero-or-more (but as few as possible) non-opening-bracket characters
($.*?$)? Then, optionally, an opening bracket and as few characters as possible until the closing bracket.
( )* Wrapped in a capturing group repeated zero-or-more times
( ) Wrapped in a capturing group to be able to reference the entire matched item
(,|$) Followed by either a comma or the end-of-string.

就像这样：

Oracle 11g R2架构设置：

CREATE TABLE table_name ( ID, Kategory ) AS
SELECT 1, 'ATD 5(2830),ATO 4(510),EDI 1,EH A1,SCI 2,SS 1,STO-SE 1(oral, CNS, blood),STO-SE 2(oral, respiratory effects)' FROM DUAL;

查询1 ：

SELECT ID,
       l.COLUMN_VALUE AS item,
       REGEXP_SUBSTR(
         Kategory,
         '(([^(]*?(\(.*?\))?)*)(,|$)',
         1,
         l.COLUMN_VALUE,
         NULL,
         1
       ) AS value
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL < REGEXP_COUNT( t.Kategory, '(([^(]*?(\(.*?\))?)*)(,|$)' )
           )
           AS SYS.ODCINUMBERLIST
         )
       ) l

：

Results:

| ID | ITEM |                               VALUE |
|----|------|-------------------------------------|
|  1 |    1 |                         ATD 5(2830) |
|  1 |    2 |                          ATO 4(510) |
|  1 |    3 |                               EDI 1 |
|  1 |    4 |                               EH A1 |
|  1 |    5 |                               SCI 2 |
|  1 |    6 |                                SS 1 |
|  1 |    7 |          STO-SE 1(oral, CNS, blood) |
|  1 |    8 | STO-SE 2(oral, respiratory effects) |

这篇关于将列文本拆分为行（在括号中提取定界符）ORACLE SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！