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?

E.g.:

COUNTRY CITY DISTRICT STREET
======= ======= ======== ==========
Spain Madrid Chueca
Ukrania
UK London Oxford St.

I'd like:

Spain; Madrid; Chueca
Ukrania
UK; London; Oxford St.

.... rather than:

Spain; Madrid; Chueca;
Ukrania; ; ;
UK; London; ; Oxford St.


I'm running Oracle9i Enterprise Edition Release 9.2.0.1.0. Thank you in
advance.


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

On Feb 4, 3:08*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
SQL> --
SQL> -- Create table
SQL> --
SQL> create table location(
2 country varchar2(30),
3 city varchar2(30),
4 street varchar2(30)
5 );

Table created.

SQL>
SQL> --
SQL> -- Insert sample data
SQL> --
SQL> insert all
2 into location
3 values('Spain','Madrid','Chueca')
4 into location
5 values('Ukrania',null,null)
6 into location
7 values('UK','London','Oxford St.')
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Plain vanilla concat query
SQL> --
SQL> -- Produces undesired results
SQL> --
SQL> select country||';'||city||';'||street
2 from location;

COUNTRY||';'||CITY||';'||STREET
--------------------------------------------------------------------
Spain;Madrid;Chueca
Ukrania;;
UK;London;Oxford St.

SQL>
SQL> --
SQL> -- Yet another try
SQL> --
SQL> -- Uses UNION to produce the desired
SQL> -- results
SQL> --
SQL> select concat_add
2 from
3 (select country||';'||city||';'||street concat_add, rownum rn
4 from location
5 where country is not null
6 and city is not null
7 and street is not null
8 union
9 select country||';'||city concat_add, rownum rn
10 from location
11 where country is not null
12 and city is not null
13 and street is null
14 union
15 select country||';;'||street concat_add, rownum rn
16 from location
17 where country is not null
18 and street is not null
19 and city is null
20 union
21 select country concat_add, rownum rn
22 from location
23 where city||street is null)
24 order by rn;

CONCAT_ADD
--------------------------------------------------------------------
Spain;Madrid;Chueca
Ukrania
UK;London;Oxford St.

SQL>

There may be a more elegant solution, I simply don't have any more
time to work on this.


David Fitzjarrell

ddf escribió:
My own workaround for a specific application I just had to finish ASAP
was an chaotic series of CASE ... END statements that would scare you
away. That's why I'm longing for something more, well, elegant.

Thank you for your time.



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

On Feb 4, 12:03*pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
My attempt may not handle the case as you would like it where the city
is NULL but the country and street are not:

SQL> select concat_add
2 from
3 (select country||';'||city||';'||street concat_add, rownum rn
4 from location
5 where country is not null
6 and city is not null
7 and street is not null
8 union
9 select country||';'||city concat_add, rownum rn
10 from location
11 where country is not null
12 and city is not null
13 and street is null
14 union
15 select country||';;'||street concat_add, rownum rn
16 from location
17 where country is not null
18 and street is not null
19 and city is null
20 union
21 select country concat_add, rownum rn
22 from location
23 where city||street is null)
24 order by rn;

CONCAT_ADD
------------------------------------------------------------------
Poland;;Plock St.
Spain;Madrid;Chueca
Michigan;Detroit
Ukrania
UK;London;Oxford St.
Alberta;Edmonton;High St.

6 rows selected.

SQL>

Notice the first entry, which conforms to the situation I described.

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


David Fitzjarrell

On 04.02.2009 19:42, ddf wrote:
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?

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

select rtrim(
case when country is null then '' else country || ';' end
from
location

You can probably achieve the same with DECODE:

select rtrim(
decode(country , null, '', country || ';')
from
location

Note: both untested.

Cheers

robert

=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?= (alvaro.NOSPAMTHANX (AT) demogracia (DOT) 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?

: E.g.:

assuming the separator cannot be the last character of any of the columns,
e.g.

rtrim(
nvl2( col1 , col1||';', '') ||
nvl2( col2 , col2||';', '') ||
nvl2( col3 , col3||';', '') ||
col4
, ';')

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

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