Re: Concatenation with separator
From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Mon, 09 Feb 2009 12:07:33 +0100
Message-ID: <gmp2po$u88$1_at_nntp.motzarella.org>
Robert Klemme escribió:
>
> 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?
Date: Mon, 09 Feb 2009 12:07:33 +0100
Message-ID: <gmp2po$u88$1_at_nntp.motzarella.org>
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!
-- -- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web: http://bits.demogracia.com -- Mi web de humor al baño María: http://www.demogracia.com --Received on Mon Feb 09 2009 - 05:07:33 CST