Обсуждение: LIKE '%%' does not return NULL

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.

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.

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