List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 11 2000 10:01am
Subject:Re: % wildcard not finding empty VARCHAR records?
View as plain text  
Hi!

I was scanning through old mails to check for unsolved things and
stumbled across the following:

>>>>> "sinisa" == sinisa  <sinisa@stripped> writes:

sinisa> Thomas David Kehoe writes:
>> I'm also having problams with the % wildcard:
>> 
>> SELECT * FROM TABLE_NAME
>> WHERE FIRST_NAME LIKE '%';
>> 
>> This returns all records with a first name, in other words, all first names
>> are found.  But it doesn't return records in which the FIRST_NAME field is
>> empty.  This seems to be a problem with VARCHAR fields, but not a problem
>> with ENUM, SET, or DATE fields.  How can I SELECT all records, empty or not
>> empty?
>> -- 
>> Stuttering Science & Therapy Website     http://www.fluencydevices.com
>> Thomas David Kehoe       Casa Futura Technologies       (888) FLU-ENCY
>> 
>> 

sinisa> HI!

sinisa> This is expected behaviour. '%' means any char, and empty attribute
sinisa> does not include even one char.

sinisa> To get all names, you should run:

sinisa> SELECT * FROM TABLE_NAME WHERE FIRST_NAME LIKE '%' AND FIRST_NAME='';

sinisa> Sinisa

Actually the above is not the expected behaveour as "%" should also
match the empty string:

mysql> select "a" like "%";
+--------------+
| "a" like "%" |
+--------------+
|            1 |
+--------------+
1 row in set (0.23 sec)

mysql> select "" like "%";
+-------------+
| "" like "%" |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Are you sure you didn't have NULL in your first_name field?

Try:

SELECT * FROM TABLE_NAME WHERE FIRST_NAME LIKE '%' OR FIRST_NAME IS NULL;

or even simpler:

SELECT * FROM TABLE_NAME;

Regards,
Monty
Thread
% wildcard not finding empty VARCHAR records?Thomas David Kehoe8 Dec
  • Re: % wildcard not finding empty VARCHAR records?sinisa9 Dec
    • Re: % wildcard not finding empty VARCHAR records?Michael Widenius11 Jan