Re: concatenating with NULLs

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

In response to

Browse pgsql-general by date

  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