Re: How concat 3 strings if 2 are not empty? - Mailing list 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 |      5PostgreSQL | 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
 
-------------------PostgreSQLisgreatPostgreSQLisPostgreSQL
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