concatenating with NULLs

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.

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, '');

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 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

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

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

Using a CASE construct is good when you need non-string output but COALESCE
is functionality equivalent and much less verbose when doing a default
string output for null values.

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Brent Wood
Sent: Monday, April 25, 2011 6:33 PM
To: spluque@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] concatenating with NULLs

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

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

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.