| From: | Seb <spluque(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: concatenating with NULLs |
| Date: | 2011-04-25 22:42:47 |
| Message-ID: | 87bozuq7fc.fsf@kolob.sebmags.homelinux.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi,
Thanks for all the helpful suggestions everyone!
Cheers,
Seb
On Tue, 26 Apr 2011 10:32:59 +1200,
"Brent Wood" <b(dot)wood(at)niwa(dot)co(dot)nz> wrote:
> Hi Seb, Use CASE to change nulls to empty strings (or a placeholder)
> as below.
> See:
> http://stream.boxwatch.ru/docs/9.0/static/functions-conditional.html
> if you want a placeholder in the result to indicate the presence of a
> null, try the second SQL:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> test=# select (case when 'a' isnull then '' else 'a' end) || (case
> when 'b' isnull then '' else 'b' end) || (case when NULL is null then
> '' end); ?column? ---------- ab (1 row)
> test=# select (case when 'a' isnull then '_' else 'a' end) || (case
> when 'b' isnull then '_' else 'b' end) || (case when NULL is null then
> '_' end); ?column? ---------- ab_ (1 row)
> test=#
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> HTH,
> Brent Wood
> Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand
>>>> Seb <spluque(at)gmail(dot)com> 04/26/11 10:21 AM >>>
> Hi,
> A query such as:
> SELECT 'a' || 'b' || NULL;
> returns a NULL. How can I ensure I get 'ab' in the result? I'm trying
> to concatenate columns and one of them might have NULL values, which I
> simply want to ignore.
> Cheers,
> -- Seb
> Please consider the environment before printing this email.
> NIWA is the trading name of the National Institute of Water &
> Atmospheric Research Ltd.
--
Seb
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Johnston | 2011-04-25 22:52:40 | Re: concatenating with NULLs |
| Previous Message | Brent Wood | 2011-04-25 22:32:59 | Re: concatenating with NULLs |