Re: select NULL||'abc' returns empty string (or NULL) - Mailing list pgsql-general

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.Elphick@lfix.co.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