Re: How concat 3 strings if 2 are not empty?

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)
>>
>>   
>
>