Hi, Thanks for all the helpful suggestions everyone! Cheers, Seb On Tue, 26 Apr 2011 10:32:59 +1200, "Brent Wood" <b.wood@xxxxxxxxxx> 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@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
