MySQL I : Text Searches

1. Logon to the MySQL Monitor client.
2. Use the accounting database: USE accounting;
3. Make sure the expenses table is populated: SELECT * FROM expenses;
4. Create a full text index:
ALTER TABLE expenses ADD FULLTEXT (expense_description);

– a full text index must be created on the columns you wish to search.
5. Do a full text search using the word “visual”:
SELECT expense_id, expense_description FROM expenses
WHERE MATCH (expense_description) AGAINST (‘visual’);
6. Search for multiple words and sort by relevancy:
SELECT expense_id,
MATCH (expense_description) AGAINST (‘visual guide’)
AS rel, expense_description
FROM expenses
WHERE MATCH (expense_description)
AGAINST (‘visual guide’);
7. Using regular expressions, find which clients have a toll-free number:
SELECT client_name, client_phone FROM clients
WHERE client_phone REGEXP ‘(800)|(888)|877’;
8. Using regular expressions, retrieve all valid contact email addresses:
SELECT client_name, contact_first_name, contact_email FROM clients
WHERE contact_email REGEXP ‘[[:alnum:]]+@.*\.[[:alnum:]]{2,3}’;

– See page 273 for a table of regular expression characters.
– PERL Poetry: using regular expressions is powerful, but does not promote code readability. Perl gets its bad reputation mostly from regular expressions.