null check and string concatenation

Question

Can I do better than this query?

 select func_returns_str('key1') || 
        nvl2( func_returns_str('key2'), 
              ',' || func_returns_str('key2'), 
              null) 
   from dual;

I would like to avoid calling func_returns_str('key2') twice. func_returns_str returns a varchar.

Elaborating further: func_returns_str could be a generic function like substr() in Oracle which you can't change the definition.

Answer 1

You can create a wrapper for the nvl2 call:

create or replace function add_comma(s varchar) return varchar as
begin
  return case when s is null then null else ','||s end;
end;
/

with t as (select 'key2' as val from dual union all select null from dual)
select val, nvl2( substr(val, 3, 2), ','||substr(val, 3, 2), null) as orig, 
       add_comma(substr(val, 3, 2)) as wrapped
from t;

VAL  ORIG WRAPPED
---- ---- -------
key2 ,y2  ,y2    

alternatively you can use a with clause (also know as a CTE ):

with t as (select substr('key2', 3, 2) as s from dual)
select nvl2(s, ','||s, null) from t;

Answer 2

You could create a function to wrap func_returns_str in that could be used for the later calls and appends the comma itself if the return value is not null.