title: Oracle: Concat with delimiter, but only if both operands are NOT NULL
body: <p>I want to select a concatenation of a couple of fields, but with a separator between them. The separator should only be there if both operands are not null.</p>
  
  <p>So for a record with <code>a='foo', b=NULL, c='bar'</code>, I want to get the result <code>abc='foo;bar'</code> (not <code>'foo;;bar'</code>).</p>
  
  <p>I would like to have a function like <code>concat_sep(a, b, ';')</code> that only adds the ';' inbetween if both a and b are not null.</p>
  
  <p>Of course, I can use nvl2 like this:</p>
  
  <pre><code>select
    a, b, c, 
    substr(abc, 1, length(abc) - 1) as abc
  from
    (select
      a, b, c, 
      nvl2(a, a || ';', '') || nvl2(b, b || ';', '') || nvl2(c, c || ';', '') as abc
    from
      Table1)
  </code></pre>
  
  <p>But as you can see, this code becomes cloggy soon, especially when you got more than 3 columns and you've given them sensible names instead of a, b and c. ;-)</p>
  
  <p>I couldn't find a shorter, easier or more readable way, but I thought I'd ask here before giving up entirely (or waste time writing such a function myself).</p>