Concatenation respecting conventional null handling

Question

The conventional handling of null in SQL, and the language specification, is that if any part of an expression is null, the whole expression is null.

However in Oracle, text concatenation converts null to a <blank>, eg:

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

I want the conventional behaviour, where the result would be null if any term is null.

Is there a method or function provided by Oracle that concatenates text using a single expression, without recoding any term, such that if any term is null, the result is null?

---

Notes:

• I don't want to repeat any terms, which would be required by a case etc, because the terms are very long and complex, and besides it's bad practice to repeat code
• I can’t define any functions. I must use just a single SQL query using nothing but standard syntax and plain Oracle provided functions/operators
• Side-stepping the no-repeat requirement by using a subquery or a CTE isn’t answering the question, it’s avoiding it: I want to know if Oracle can concatenate Strings using a single expression in the same way every other database I know does

Answer 1

I'm not avere of a SQL function and this NULL behavior on VARCHAR2 is posible conventional, but for sure not usual expected. The reason is that Oracle doesn't distinct betwen NULL and a string with length zero (''). For string concatenation the NULLs are considered as empty strings.

Anyway you may use subqueries to avoid repeating the expressions:

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;

returns

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

foo baz bar foobazbar

Alternatively you may write the predicate in teh CASE statement a bit more compact using the Group Comparison Conditions

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

Unfortunately the Group Comparison Conditions doesn't allow a dierct IS NULL test, so a workaround with length must be used.

The third option is a bit ugly (as requires some special string that doesn't exists in regular strings, but probably meets best your requriements.

Simple NVL all strings before concatenation and than exclude those mappend by NVL

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;

Answer 2

Yes, concat and || do not work in a standard (as in SQL92 spec) way. I guess, this is because there is no distinction between an empty string and null value in Oracle DB.

You can create a user defined function and use it in SQL.

CREATE OR REPLACE FUNCTION standard_concat (
    a VARCHAR2,
    b VARCHAR2
) RETURN VARCHAR2
    AS
BEGIN
    IF
        a IS NULL OR b IS NULL
    THEN
        RETURN NULL;
    ELSE
        RETURN a || b;
    END IF;
END;
/

Using this function gives you these results:

select standard_concat(standard_concat('foo', ''), 'bar') from dual; returns null

select standard_concat(standard_concat('foo', null), 'bar') from dual; returns null

select standard_concat(standard_concat('foo', 'foo'), 'bar') from dual; returns "foofoobar"

As you can see, empty string will be treated as null since this is the way Oracle DB treats Strings. There is no way to make a distinction here.

If this function is only needed for one query you can inline your function definition into the SQL itself as in:

WITH
    FUNCTION standard_concat (
        a VARCHAR2,
        b VARCHAR2
    ) RETURN VARCHAR2
        AS
    BEGIN
        IF
            a IS NULL OR b IS NULL
        THEN
            RETURN NULL;
        ELSE
            RETURN a || b;
        END IF;
    END;
SELECT
    standard_concat(standard_concat('foo',''),'bar')
FROM
    dual;

I hope it helps.

Answer 3

There ins't a single expression that will do what you want unfortunately - there isn't a stanrd-compliant equivalent of concat() or the concatenation operator.

I'm sure this doesn't meet the criteria either, but as requested (if slightly mis-advertised), an 'XNL' (well, XMLDB anyway) workaround/hack/abomination:

set null "(null)"

select xmlquery(
  'if (min((string-length($x), string-length($y), string-length($z))) = 0) 
    then "" else concat($x, $y, $z)'
  passing 'foo' as "x", null as "y", 'bar' as "z"
  returning content)
from dual;

XMLQUERY('IF(MIN((STRING-LENGTH($X),STRING-LENGTH($Y),STRING-LENGTH($Z)))=0)THEN
--------------------------------------------------------------------------------
(null)

With no null expressions:

select xmlquery(
  'if (min((string-length($x), string-length($y), string-length($z))) = 0) 
    then "" else concat($x, $y, $z)'
  passing 'foo' as "x", 'bar' as "y", 'baz' as "z"
  returning content)
from dual;

XMLQUERY('IF(MIN((STRING-LENGTH($X),STRING-LENGTH($Y),STRING-LENGTH($Z)))=0)THEN
--------------------------------------------------------------------------------
foobarbaz

As well as being a hack, of course, it suffers from the problem of not being general as the number of terms is fixed. I haven't come up with a way to pass any number of terms in.