Re: LIKE '%%' does not return 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