Re: Concatenation with separator

From: Álvaro G. Vicario <>
Date: Mon, 09 Feb 2009 12:07:33 +0100
Message-ID: <gmp2po$u88$>

Robert Klemme escribió:
>> 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?

I'm not doing any sort of export. I'm just fetching data from the database to feed an application with it.

> :-) 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

The RTRIM() trick is so simple that it makes me feel ashamed for not having found it myself. Thank you very much!

-- - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web:
-- Mi web de humor al baño María:
Received on Mon Feb 09 2009 - 05:07:33 CST

Original text of this message