Thread: How concat 3 strings if 2 are not empty?
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' Regards Andreas
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
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) > >
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) >> >> > >