拆分时，SQL REGEXP_SUBSTR返回null字符串 [英] SQL REGEXP_SUBSTR returns null String when splitting

问题描述

我按照本文中概述的说明将分隔的字符串拆分为表的行:

I followed the instructions outlined in this post to split a delimited string into rows of a table:

在Oracle中将字符串拆分为多行

在这个小提琴中表示了对特定分隔字符串有用的答案:

The answer that worked for that particular delimited string is represented in this fiddle:

Demo1

with temp as
(
    select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name;

很遗憾，我的字符串不是逗号分隔的.它由子字符串':::'分隔.我试图通过在下面的小提琴中编写SQL来改变答案，以适合我的情况:

Unfortunately my string is not comma delimited. It is delimited by the substring ':::'. I attempted to alter the answer to suit my situation by writing the SQL in the following fiddle:

Demo2

with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.error, '[^:::]+'))  + 1) as sys.OdciNumberList)) levels
order by name

如您所见，我更改了测试字符串，以':::'分隔，并更改了正则表达式以使其匹配，但是查询生成了一个多余的行，其中所返回的子字符串的值为Null.

As you can see I altered the test string to be delimited by ':::' and altered the regular expression to match, but the query is producing an extraneous row with a Null value for the substring returned.

有人可以帮助我理解为什么我所做的更改会产生具有Null值的无关行吗?

Can anyone help me understand why the changes I made would be producing that extraneous row with the Null value?

推荐答案

只需将REPLACE和标准代码与,

SqlFiddleDemo

SqlFiddleDemo

 with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(REPLACE(t.error, ':::', ', '), '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(REPLACE(t.error, ':::', ', '), '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name

或者您需要用分度计的长度除以

Or you need to divide by length of delimeter:

SqlFiddle

SqlFiddle

with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1:::Err2' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
       regexp_replace(t.error, '[^:::]+'))/3  + 1) as sys.OdciNumberList)) levels
order by name

您可以看到执行的原因:

You can see why executing:

SELECT length (regexp_replace('Err1:::Err2:::Err3', '[^:::]+')) + 1 AS l
FROM dual

这将返回7和您的:

SELECT DISTINCT  t.name, t.project,
trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error

将尝试获取7次出现的regexp_substr，其中4次出现NULL，最后4次NULL被DISTINCT压缩为一个NULL.

will try to get regexp_substr for 7 occurences where 4 of them will be NULL and in the end 4 NULL will be squashed to one NULL by DISTINCT.

这篇关于拆分时，SQL REGEXP_SUBSTR返回null字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！