Обсуждение: How concat 3 strings if 2 are not empty?

Поиск
Список
Период
Сортировка

How concat 3 strings if 2 are not empty?

От
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'


Regards
Andreas

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

От
"A. Kretschmer"
Дата:
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

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

От
Andreas
Дата:
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)
>
>

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

От
"M.P.Dankoor"
Дата:
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)
>>
>>   
>
>