Re: concatenating with NULLs

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.

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