From: Frank Finner <postgresql@finner.de>
To: Traci Sumpter <Traci.Sumpter@opus.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: LIKE '%%' does not return NULL
Date: Sun, 15 Aug 2004 21:17:49 +0200
Message-ID: <20040815211749.115a9f86.postgresql@finner.de> (raw)
In-Reply-To: <KFEJJKOAGCCGEJFLKCBBKEALEBAA.Traci.Sumpter@opus.co.nz>
References: <KFEJJKOAGCCGEJFLKCBBKEALEBAA.Traci.Sumpter@opus.co.nz>
Hi,
you should additionally check with IS NULL:
SELECT * FROM mytable where myfield like '%%' OR myfield IS NULL
if you want to get fields containing NULL as well. A field containing "NULL" in
fact contains nothing, not even an empty string, so you cannot catch it with
"%".
BTW, this holds true especially for booleans: They can contain the values "true"
or "false" or no value at all, which means, they contain "NULL" and are in fact
undefined. You won´t catch them with something like "SELECT * FROM bla where
blubb <> false", you will only get the fields containing true, not the NULLs.
Regards, Frank.
On Wed, 11 Aug 2004 13:22:00 +1200 "Traci Sumpter" <Traci.Sumpter@opus.co.nz>
sat down, thought long and then wrote:
> A team developer has chosen the lazy way of not checking if a variable
> exists on his PHP page and has code which produces the following SQL
>
> SELECT * FROM mytable where myfield ilike '%%'
>
> I have noticed that this statement does not return null or empty myfield
> records.
>
> Is this the way (SQL) to do this??
>
> Is there a better syntax to the SQL??
>
> Is the better way to create the statement in PHP is to check if the passed
> value <> '' ??
>
> Or is this issue being fixed in the new V8 version of postgreSQL.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@finner.de, Traci.Sumpter@opus.co.nz
Subject: Re: LIKE '%%' does not return NULL
In-Reply-To: <20040815211749.115a9f86.postgresql@finner.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox