Re: Concatenation with separator

From: ddf <oratune_at_msn.com>
Date: Wed, 4 Feb 2009 08:20:14 -0800 (PST)
Message-ID: <b9bcc101-cf74-496b-8d38-dce096114ee2_at_s9g2000prg.googlegroups.com>

On Feb 4, 3:08 am, "Á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?
>
> 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
> --

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 Received on Wed Feb 04 2009 - 10:20:14 CST