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

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?