时间戳

首页 > 解决方案 > 遵守传统空值处理的连接

oracle - 遵守传统空值处理的连接

问题描述

SQL 和语言规范中的常规处理null是，如果表达式的任何部分是null，则整个表达式是null。

但是在 Oracle 中，文本连接转换null为 a <blank>，例如：

select concat(concat('foo', null), 'bar') from dual;  --> returns "foobar"
select 'foo' || null || 'bar' from dual;              --> returns "foobar"

null我想要传统的行为，如果任何术语为空，结果将是。

Oracle 是否提供了一种方法或函数，它使用单个表达式连接文本，而不重新编码任何术语，这样如果任何术语是null，结果是null？

笔记：

标签： oraclenullconcatenationoracle12c

解决方案

我不喜欢一个SQL函数，并且这种 NULL 行为VARCHAR2是可能的常规行为，但肯定不是通常预期的。NULL原因是 Oracle 在和长度为零的字符串 ('')之间没有区别。对于字符串连接，NULL 被视为空字符串。

无论如何，您可以使用子查询来避免重复表达式：

with t1 as (
select 'foo' col1, null col2, 'bar' col3 from dual union all
select null col1, null col2, null col3 from dual union all
select 'foo' col1, 'baz' col2, 'bar' col3 from dual
) 
select  col1,col2,col3,
case when col1 is not NULL and col2 is not NULL and col3 is not NULL then
  col1||col2||col3 end as concat
from t1;

返回

COL COL COL CONCAT   
--- --- --- ---------
foo     bar          

foo baz bar foobazbar

或者，您可以使用组比较条件在 tehCASE语句中编写更紧凑的谓词

select  
case when 0 < ALL(length(col1),length(col2),length(col3)) then
  col1||col2||col3 end as concat
from t1;

不幸的是，组比较条件不允许直接IS NULL测试，因此length必须使用解决方法。

第三个选项有点难看（因为需要一些常规字符串中不存在的特殊字符串，但可能最符合您的要求。

在连接之前简单NVL所有字符串，而不是排除那些 mappend byNVL

with t2 as 
(select  nvl(col1,'#§$%')||nvl(col2,'#§$%')||nvl(col3,'#§$%') as concat
from t1)
select  
case when concat not like '%#§$\%%' escape'\' then concat end as concat
from t2;

推荐阅读