英文:
How can we separate multiple string in Oracle?
问题
在Oracle中，我们如何将“a”，“b”，“c”，“d”分开？最初我在使用REGEXP_SUBSTR('a;b;c;d','[^;]+',1,level)通过分号“;”作为分隔符来分隔数值，但存在一个情况，即数值本身包含分号分隔符，这会导致找不到记录的错误。
英文:
How can we separate "a","b","c","d" into a, b, c, d in Oracle?
Originally I was using REGEXP_SUBSTR('a;b;c;d','[^;]+',1,level) to separate the value by delimiter semicolon ";", but there is a case where the value itself contains the delimiter semicolon and it causes no records found error
答案1
得分: 1
假设您的数据是JSON兼容的（其中字面值内的引号以反斜杠转义，并且字面值是逗号分隔的），则使用 JSON_TABLE：
SELECT j.value
FROM table_name t
CROSS APPLY JSON_TABLE(
''['' || t.list || '']'',
'$[*]'
COLUMNS
value VARCHAR2(10) PATH '$'
) j
对于示例数据：
CREATE TABLE table_name(list VARCHAR2(100));
INSERT INTO table_name (list) VALUES ( ''"a","b","c","d"' );
INSERT INTO table_name (list) VALUES ( ''"noquotes","\"quotes\"","comma,here"' );
输出：
| VALUE |
|---|
| a |
| b |
| c |
| d |
| noquotes |
| "quotes" |
| comma,here |
如果您的数据是CSV兼容的（其中字符串字面值中的引号通过加倍来转义，字面值用逗号分隔，但可以是任何分隔符），则可以使用递归子查询和正则表达式：
WITH matches (list, match, epos) AS (
SELECT list,
REPLACE(REGEXP_SUBSTR(list, ''"(([^'"]|"")+)"(,|$)'', 1, 1, NULL, 1), ''""', ''"'),
REGEXP_INSTR(list, ''"(([^'"]|"")+)"(,|$)'', 1, 1, 1)
FROM table_name
UNION ALL
SELECT list,
REPLACE(REGEXP_SUBSTR(list, ''"(([^'"]|"")+)"(,|$)'', epos, 1, NULL, 1), ''""', ''"'),
REGEXP_INSTR(list, ''"(([^'"]|"")+)"(,|$)'', epos, 1, 1)
FROM matches
WHERE epos > 0
AND epos < LENGTH(list)
)
SEARCH DEPTH FIRST BY list SET order_id
SELECT match
FROM matches
对于示例数据：
CREATE TABLE table_name(list VARCHAR2(100));
INSERT INTO table_name (list) VALUES ( ''"a","b","c","d"' );
INSERT INTO table_name (list) VALUES ( ''"noquotes","""quotes""","comma,here"' );
输出：
| MATCH |
|---|
| a |
| b |
| c |
| d |
| noquotes |
| "quotes" |
| comma,here |
英文:
Assuming your data is JSON compatible (where quotes inside literals are escaped with a backslash and literals are comma-separated) then use JSON_TABLE:
SELECT j.value
FROM table_name t
CROSS APPLY JSON_TABLE(
'[' || t.list || ']',
'$[*]'
COLUMNS
value VARCHAR2(10) PATH '$'
) j
Which, for the sample data:
CREATE TABLE table_name(list VARCHAR2(100));
INSERT INTO table_name (list) VALUES ( '"a","b","c","d"' );
INSERT INTO table_name (list) VALUES ( '"noquotes","\"quotes\"","comma,here"' );
Outputs:
| VALUE |
|---|
| a |
| b |
| c |
| d |
| noquotes |
| "quotes" |
| comma,here |
If your data is CSV compatible (where quotes in string literals are escaped by doubling them up and literals are separated with commas, but could be any delimiter) then you can use a recursive sub-query and regular expressions:
WITH matches (list, match, epos) AS (
SELECT list,
REPLACE(REGEXP_SUBSTR(list, '"(([^"]|"")+)"(,|$)', 1, 1, NULL, 1), '""', '"'),
REGEXP_INSTR(list, '"(([^"]|"")+)"(,|$)', 1, 1, 1)
FROM table_name
UNION ALL
SELECT list,
REPLACE(REGEXP_SUBSTR(list, '"(([^"]|"")+)"(,|$)', epos, 1, NULL, 1), '""', '"'),
REGEXP_INSTR(list, '"(([^"]|"")+)"(,|$)', epos, 1, 1)
FROM matches
WHERE epos > 0
AND epos < LENGTH(list)
)
SEARCH DEPTH FIRST BY list SET order_id
SELECT match
FROM matches
Which, for the sample data:
CREATE TABLE table_name(list VARCHAR2(100));
INSERT INTO table_name (list) VALUES ( '"a","b","c","d"' );
INSERT INTO table_name (list) VALUES ( '"noquotes","""quotes""","comma,here"' );
Outputs:
| MATCH |
|---|
| a |
| b |
| c |
| d |
| noquotes |
| "quotes" |
| comma,here |
答案2
得分: -1
如果分隔符被用在要分隔的标记内部，它就不再是一个分隔符，只有在你有一种方法可以转义标记内部的分隔符或者引用包含分隔符的标记时，你才能分隔字符串：
a;b\;c;d 或者 a;"b;c",d 但 a;b;c;d 将永远不明确。
英文:
If the delimiter is used inside the tokens to be separated, it's no more a delimiter, you will be able to separate the strings only if you have a way to escape the delimiter inside the tokens or quote the tokens containing the delimiter:
a;b\;c;d or a;"b;c",d but a;b;c;d is forever ambiguous.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论