[RESOLVED] wildcards -- don't understand

bbsmfg3

I have a talbe named "friends" with a field named "how". The field "how" has many records some with only an "M".
I have the code:

$res=$sql->query("SELECT count(*) FROM `friends` WHERE `how`='%M%' ");
$row=$sql->getrow($res);

the code above works, but I understand wildcards are supposed to be used with the "like" command. If I try this with out the % wildcards it fails. I've tried nearly every combination I can think of using the "like" command and they all fail.
If I leave out the 2 % it also fails, even though the "M" is the only character in these records

*******************Why?**************************

djjjozsi

see here the usage the LIKE command.

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

bbsmfg3

I know, If read that and many others. Still do not understand why the wildcards works without the "like" command. But it does.

I do not like leaving the code like it is without the "like" command but have not found another alternative.

djjjozsi

i'm not sure:

from one of my table:

0 results
SELECT * FROM gen_tipus WHERE tipus_neve='t%'

2 results
SELECT * FROM gen_tipus WHERE tipus_neve LIKE 't%'

bbsmfg3

It's a good thing none of the other records contained an "M".
Problem is, many of the records that are supposed to contain only an "M" have a null character there also.
I exported the table to a csv file and sure enough it contains NULL characters. A lot of them.
I have the NULL attrib of the table structure set to "NO" for all fields. I can use the trim command to trim the null character before using the variable containing this field, BUT that doesn't help the Select command.
Is there a way to scan the entire table and remove the null characters from all fields?

bradgrafelman

Try something like:

UPDATE myTable
SET col1 = REPLACE(col1, '\0', ''),
col2 = REPLACE(col2, '\0', ''),
# etc. for all columns

If you're feeling lazy and/or fancy, you could have PHP automate this process by getting a list of tables from the DB, getting the columns in each table, and executing a dynamically-built query for each table.

bbsmfg3

Thanks, that worked.