Andreas,
Kretschmer was quite close, try following:
case
when trim(coalesce(s1,'')) = '' and trim(coalesce(s3,'')) = '' then ''
when trim(coalesce(s1,'')) != '' and trim(coalesce(s2,'')) != '' and
trim(coalesce(s3,'')) != '' then s1 || s2 || s3
else trim(coalesce(s1,'')) || trim(coalesce(s3,''))
end
Mario
Andreas wrote:
No.
B should only appear if A and C are not empty.
B is just a filler.
Thanks
Andreas
A. Kretschmer schrieb:
In response to Andreas :
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)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
