将分隔的字符串转换为 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.
相关文章