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

On Wed, 2002-02-27 at 11:25, 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?

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

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"If we confess our sins, he is faithful and just to
forgive us our sins, and to cleanse us from all
unrighteousness." I John 1:9