Skip site navigation (1) Skip section navigation (2)

Re: select NULL||'abc' returns empty string (or NULL)

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Holger Marzen <holger(at)marzen(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: select NULL||'abc' returns empty string (or NULL)
Date: 2002-02-27 12:00:20
Message-ID: 20020227230020.B27322@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Feb 27, 2002 at 12:25:54PM +0100, Holger Marzen wrote:
> I suppose that PostgreSQL insists that a NULL value cannot be
> concatenated with a string. Can I cast this somehow? I noticed that
> feature when I did a
> 
> select * from tab where col1||col2 ilike '%bla%'
> 
> and it did't find rows where one of the columns was NULL. Any ideas for
> workarounds?

You're look for the coalesce function.

coalesce(NULL,a) = a
coalesce(val,a) = val

try:

select * from tab where col1||coalesce(col2,'') ilike '%bla%';

HTH,
-- 
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?

In response to

pgsql-general by date

Next:From: Oliver ElphickDate: 2002-02-27 12:12:46
Subject: Re: select NULL||'abc' returns empty string (or NULL)
Previous:From: Alexey BorzovDate: 2002-02-27 11:56:53
Subject: Re: select NULL||'abc' returns empty string (or NULL)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group