From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How concat 3 strings if 2 are not empty? |
Date: | 2009-02-18 12:32:14 |
Message-ID: | 499BFFCE.60003@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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)
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2009-02-18 13:05:46 | Re: Funtion to clean up strings? |
Previous Message | A. Kretschmer | 2009-02-18 12:08:05 | Re: How concat 3 strings if 2 are not empty? |