Using LIKE command in MySQL gives strange results

Question

I am new to MySQL but have used Access for many years in developing the intranet. I recently started using the MySQL database and pulled my hair out. The LIKE command gives erratic or no results for entries that exist.

A table called Customers that contains the Business Name column. One such entry is, for example, "FRED SMITH CONSTRUCTIONS".

Select * from Clients where BusinessName LIKE '%FRED%' -- returns true. Select * from Clients where BusinessName LIKE '%FRED SMITH%' -- returns false! Select * from Clients where BusinessName LIKE '%FRED%SMITH%' -- returns false! Select * from Clients where BusinessName LIKE '%FRED%S%' -- returns true. Select * from Clients where BusinessName LIKE '%FRED%SM%' -- returns false! Select * from Clients where BusinessName LIKE '%FRED%S%C%' -- returns true. 

Its pretty messy and it makes no sense to me. No more than 1 word, and this is a seam to get confused. Its as a wildcard% does not work between words, or if the second word has more than one character.

To save me from troubles, I search for each word separately, but it shows too many results. eg,

 Select * from Clients where BusinessName LIKE '%FRED%' or BusinessName LIKE '%SMITH%' 

returns true.

Any suggestions? Column Type - Text. Tried RLIKE with similar results.

Answer 1

Your column character set is probably not the one you expect. MySQL installations often default to a value other than ISO8859-1.