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

postgresql returns null for null||'abc'.
null means 'unknowm' so if you concatenate something known
with something unknown, the result is unknown.

You probably want to do :

select *
    from tab
    where   (case when col1=null then '' else col1)
        ||(case when col2=null then '' else col2) ilike '%bla%';

Steve.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Holger Marzen
Envoye : mercredi 27 fevrier 2002 12:26
A : pgsql-general@postgresql.org
Objet : [GENERAL] select NULL||'abc' returns empty string (or NULL)


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?

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org