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