在空值上使用连接级别使用pl/sql拆分字符串 [英] Split string using pl/sql using connect level on null value

问题描述

我在Oracle pl/sql中使用以下代码 (版本:Oracle数据库11g版本11.2.0.1.0)

I'm using this following code in Oracle pl/sql (Version: Oracle Database 11g Release 11.2.0.1.0)

select regexp_substr('A~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('A~B~C','[^~]+')) + 1

给出以下结果

row1: A
row2: B
row3: C

那是完美的，但是我应该提供一个空值，即:

That's perfect, however should I want to give a null value, ie:

select regexp_substr('~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('~B~C','[^~]+')) + 1

我期望并想要以下东西:

I expected and wanted the following:

row1: <null>
row2: B
row3: C

但得到以下输出:

row1: B
row2: C
row3: null

我在编写pl/sql代码时出错了吗?我该如何使其正常工作?

Am I doing the pl/sql code wrong? How can I make it work right?

推荐答案

因为我在问题中使用了单个字符作为示例，但实际上，我在项目中使用了长字符串，例如"How〜do〜I〜做〜这个'

Because I used single characters as example in my question, but in practicality I'm using long strings in my project, for example 'How~do~I~do~this'

我从 OTN Oracle.com 遇到了这个解决方案，谢谢给chris227.

I came across this solution from OTN Oracle.com, thanks to chris227.

SELECT CAST(REGEXP_SUBSTR (str, '(.*?)(~|$)', 1, level, null, 1) AS CHAR(12))  output
FROM (select 'How~do~I~do~this' as str from dual)
CONNECT BY level <= regexp_count(str, '~') + 1;

这甚至可以用于单个字符.

This will work even with single characters.

希望这将有助于其他人寻找类似的解决方案.

Hope this will help others looking for similair solutions.

这篇关于在空值上使用连接级别使用pl/sql拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！