Re: Concatenation with separator

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 04 Feb 2009 21:24:16 +0100
Message-ID: <6uubrlFgq5vbU1_at_mid.individual.net>

On 04.02.2009 19:42, ddf wrote:
> On Feb 4, 12:03 pm, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.com> wrote:

> I have a series of VARCHAR2 columns that might be null or not. Is there
> a simple way to concatenate them using a separator only between non-null values?

I am bit irritated by this requirement: with this approach you won't be able to tell from which DB field a value came. If you do a CSV import in some other database or other tool, values from the same column will not properly align. Is this really what you want?

> And you're very welcome, it's an enjoyable exercise.

:-) Another approach using good old CASE and RTRIM:

select rtrim(

      case when country  is null then '' else country  || ';' end
   || case when city     is null then '' else city     || ';' end
   || case when district is null then '' else district || ';' end
   || case when street   is null then '' else street          end

   , ';') csv
from

   location

You can probably achieve the same with DECODE:

select rtrim(
      decode(country , null, '', country  || ';')
   || decode(city    , null, '', city     || ';')
   || decode(district, null, '', district || ';')
   || decode(street  , null, '', street)

   , ';') csv
from

   location

Note: both untested.

Cheers

        robert Received on Wed Feb 04 2009 - 14:24:16 CST