将分隔的字符串转换为 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 如果提供的分隔符是 ~^

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

最好的问候

推荐答案

Riffing off 这种用于拆分字符串同时允许空值的方法:

Riffing off this method for splitting a string while allowing for nulls:

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() 调用还使用可选参数 指定 subexpr - 所以它只得到第一个分组 (.*?) 而不是分隔符本身，它在第二个分组中.

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屋！