pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLIKE '%%' does not return NULL
3+ messages / 3 participants
[nested] [flat]
* LIKE '%%' does not return NULL
@ 2004-08-11 01:22 Traci Sumpter <Traci.Sumpter@opus.co.nz>
2004-08-15 18:56 ` Re: LIKE '%%' does not return NULL Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-15 19:17 ` Re: LIKE '%%' does not return NULL Frank Finner <postgresql@finner.de>
0 siblings, 2 replies; 3+ messages in thread
From: Traci Sumpter @ 2004-08-11 01:22 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LIKE '%%' does not return NULL
2004-08-11 01:22 LIKE '%%' does not return NULL Traci Sumpter <Traci.Sumpter@opus.co.nz>
@ 2004-08-15 18:56 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-08-15 18:56 UTC (permalink / raw)
To: Traci Sumpter <Traci.Sumpter@opus.co.nz>; +Cc: pgsql-sql
On Wed, 11 Aug 2004, Traci Sumpter 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.
It should not return true for NULLs but should for empty fields (which it
does AFAICS).
If you want to get the NULLs as well you'll need to explicitly OR myfield
is NULL.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LIKE '%%' does not return NULL
2004-08-11 01:22 LIKE '%%' does not return NULL Traci Sumpter <Traci.Sumpter@opus.co.nz>
@ 2004-08-15 19:17 ` Frank Finner <postgresql@finner.de>
1 sibling, 0 replies; 3+ messages in thread
From: Frank Finner @ 2004-08-15 19:17 UTC (permalink / raw)
To: Traci Sumpter <Traci.Sumpter@opus.co.nz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-15 19:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 01:22 LIKE '%%' does not return NULL Traci Sumpter <Traci.Sumpter@opus.co.nz>
2004-08-15 18:56 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-15 19:17 ` Frank Finner <postgresql@finner.de>
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