From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How concat 3 strings if 2 are not empty? |
Date: | 2009-02-18 12:08:05 |
Message-ID: | 20090218120805.GA17440@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
In response to Andreas :
> Hi,
>
> I'd like a function that concats 3 string parameters on condition the
> 1st and 3rd are NOT empty or NULL.
> xCat (s1, s2, s3)
> s2 would be a connector that appears only if s1 and s3 are set.
>
> NULL and an empty string '' should be handled the same.
>
> e.g.
> 'PostgreSQL', ' is ', ' great' --> 'PostgreSQL is great'
> NULL, ' is ', ' great' --> 'great'
> 'PostgreSQL', ' is ', NULL --> 'PostgreSQL'
> NULL, ' is ', NULL --> NULL
> 'PostgreSQL', NULL, ' great' --> 'PostgreSQL great'
Something like that?
test=*# select a,b,c, length(a), length(b), length(c) from string ;
a | b | c | length | length | length
------------+----+-------+--------+--------+--------
PostgreSQL | is | great | 10 | 2 | 5
PostgreSQL | is | | 10 | 2 |
PostgreSQL | | | 10 | |
| is | | | 2 |
| is | | 0 | 2 |
| is | | 0 | 2 | 0
(6 rows)
test=*#
test=*# select case when (a is null and c is null) or (a = '' and c = '') then null else coalesce(a,'') || coalesce(b,'')||coalesce(c,'') end from string;
case
-------------------
PostgreSQLisgreat
PostgreSQLis
PostgreSQL
is
(6 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2009-02-18 12:32:14 | Re: How concat 3 strings if 2 are not empty? |
Previous Message | Andreas | 2009-02-18 11:50:53 | How concat 3 strings if 2 are not empty? |