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
