Thread: concatenating with NULLs

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

Seb wrote:
> 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.

COALESCE is your friend:
   select 'a' || 'b' || COALESCE(columnName, '');

HTH

Bosco.

Hi Seb,

Use CASE to change nulls to empty strings (or a placeholder) as below.

See: http://www.postgresql.org/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




>>> Seb <spluque@gmail.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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

Thanks for all the helpful suggestions everyone!

Cheers,
Seb


On Tue, 26 Apr 2011 10:32:59 +1200,
"Brent Wood" <b.wood@niwa.co.nz> wrote:

> Hi Seb, Use CASE to change nulls to empty strings (or a placeholder)
> as below.

> See:
> http://www.postgresql.org/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@gmail.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

On 04/25/2011 02:13 PM, Seb wrote:
> 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.


SELECT 'a' || 'b' || coalesce(NULL, '');