In this case, I would identify a string (e.g. 'mountain view') which is a leading or white space prepended string and it should be appended only by comma, dot, exclamation mark or question mark.
After considering all the combinations, I create a statement like this:
mysql> select region from customers where region like 'mountain view.%' or region like 'mountain view,%' or region like 'mountain view.%' or region like 'mountain view!%' or region like 'mountain view?%' or region like ' mountain view.%' or region like ' mountain view,%' or region like ' mountain view.%' or region like ' mountain view!%' or region like ' mountain view?%';
You can see that it's extremely ugly and unfriendly for maintenance. So I decide to rewrite the statement to a regular expression style by using RLIKE (synonym for REGEXP).
mysql> select region from customers where region rlike '(^| )mountain view[,.!?]+';
The result is the same, but the statement s more concise and readable. For extremely long statements, you can benefit from this kind of clause.
Using REGEXP or RLIKE is a trade-off actually, you get the readability, but you lose the performance, because sometimes it's much slower than the plain old LIKE clause when the table is big. But this can be improved.
A smarter way to get it faster is to add more filters on itself or others to the where clause:
- region is not null
- length(region) > 10
- post_code between 10000 and 30000
Of course, a full text index is also helpful.