Re: Concatenation with separator
Date: Wed, 04 Feb 2009 21:24:16 +0100
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:
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
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
Note: both untested.
robert Received on Wed Feb 04 2009 - 14:24:16 CST