更新时间:2023-02-18 09:37:11
您可以使用正则表达式(([[^(] *?(\(。*?\))?)*)(,| $)
匹配:
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) |