在Oracle中将定界字符串转换为行 [英] Convert delimited string to rows in oracle

问题描述

我以前使用以下查询将逗号分隔的字符串转换为行-

I used to use below query to convert comma delimited string to rows -

select regexp_substr('A,B,C,D','[^,]+', 1, level) from dual
connect by regexp_substr('A,B,C,D', '[^,]+', 1, level) is not null;

但是，现在我的分隔符是-'〜^'

But, now my delimiter is - '~^'

我不能对此定界符使用相同的查询.

I am not able to use same query for this delimiter.

select regexp_substr('A~^B~^C~D^E','[^~^]+', 1, level) from dual
    connect by regexp_substr('A~^B~^C~D^E', '[^~^]+', 1, level) is not null;

我希望-

列

A
B
C~D^E

请帮助

或者如果提供的定界符为〜^

OR May be is it possible to get nth element if delimiter provided is ~^

最好的问候

推荐答案

关闭此方法用于在允许空值的同时拆分字符串:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, level, null, 1) from dual
connect by level < regexp_count('A~^B~^C~D^E','(.*?)(~\^|$)');

REGEXP_SUBS
-----------
A
B
C~D^E

在链接的答案中，它以非贪婪的方式查找任何字符，然后是~^(脱字符号，所以是~\^)或行尾的组合. regexp_substr()调用使用可选参数来指定-因此，它仅获得第一个分组(.*?)，而不是定界符本身，后者位于第二个分组中.

As in the linked answer it looks for any characters, non-greedily, followed by the combination of ~^ (with the caret escaped, so ~\^) or end-of-line. The regexp_substr() calls also uses the optional arguments to specify subexpr - so it only gets the first grouping (.*?) and not the delimiter itself, which is in the second grouping.

如果您想要一个特定的元素，那就更接近链接的帖子了:

If you want a specific element then that's even closer to the linked post:

select regexp_substr('A~^B~^C~D^E','(.*?)(~\^|$)', 1, 3, null, 1) from dual;

REGEX
-----
C~D^E

或者在过程中执行此操作时，如果要查看多个集合，请使用connect-by查询填充集合，然后选择所需的元素.

Or as you're doing this in a procedure, use the connect-by query to populate a collection, and then pick out the element you need, if you'll be looking at more than one.

这篇关于在Oracle中将定界字符串转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！