Обсуждение: 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

Greetings, Holger!

At 27.02.2002, 14:25, you wrote:
HM> I suppose that PostgreSQL insists that a NULL value cannot be
HM> concatenated with a string. Can I cast this somehow? I noticed that
HM> feature when I did a

HM> select * from tab where col1||col2 ilike '%bla%'

HM> and it did't find rows where one of the columns was NULL. Any ideas for
HM> workarounds?
    Yes:
    SELECT * FROM tab WHERE coalesce(col1, '')||coalesce(col2, '') ILIKE '%bla%';

--
Yours, Alexey V. Borzov, Webmaster of RDW.ru

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@svana.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?

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

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