Re: Concatenation with separator
Date: Wed, 4 Feb 2009 10:42:38 -0800 (PST)
Message-ID: <a37a6427-b338-49da-b764-745032f67bb4_at_v5g2000pre.googlegroups.com>
On Feb 4, 12:03 pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> ddf escribió:
>
>
>
>
>
> > 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;
> [...]
> > There may be a more elegant solution, I simply don't have any more
> > time to work on this.
>
> 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
> --- Hide quoted text -
>
> - Show quoted text -
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 Received on Wed Feb 04 2009 - 12:42:38 CST