From: | "Steve SAUTETNER" <ssa(at)informactis(dot)com> |
---|---|
To: | <holger(at)marzen(dot)de>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: select NULL||'abc' returns empty string (or NULL) |
Date: | 2002-02-27 12:39:11 |
Message-ID: | BKEHJDOFOAHJBCOBCHJGIEBNCMAA.ssa@informactis.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]De la part de Holger Marzen
Envoye : mercredi 27 fevrier 2002 12:26
A : pgsql-general(at)postgresql(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2002-02-27 13:43:28 | Re: Timestamp output |
Previous Message | Oliver Elphick | 2002-02-27 12:12:46 | Re: select NULL||'abc' returns empty string (or NULL) |