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.

• SqlSandwiches: Can you provide any more information about the table? What kind of results you are looking for? What does func_returns_str do besides return a varchar (lookup, etc)?
• Shannon Severance: With a built in functions, Oracle may know which ones are idempotent and just re-use the return value instead of calling twice, without you needing to change your code. SQL is declarative, not imperative. Not leaving as an answer because I'm unsure how to prove either way.

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.