MySQL Search Refinement (NSFW)

Question

Hey , i got this MySQL Query :

select query from HR_Health_Logs where query REGEXP 'masturbation|masturbate|masturbated|wank|wanking|wanked|masturbat|mansturbation|marstabation|marturbation|mastabation|mastarbation|master basion|masterbate|masterbation|masterbeting|masturation|masturbatin|masturbating|masturbatn|masturbtion|mensturbation|mesitarbation|murstabation|mursturbation|mustabate|mustabetion|mustarbation|musterabuion|musterbation|musturbated|musturbation|masterbasion|masturbates|wanks|wankings|masturbats|mansturbations|marstabations|marturbations|mastabations|mastarbations|master basions|masterbates|masterbations|masterbetings|masturations|masturbatins|masturbatings|masturbatns|masturbtions|mensturbations|mesitarbations|murstabations|mursturbations|mustabates|mustabetions|mustarbations|musterabuions|musterbations|musturbateds|musturbations|masterbasions';

I need to cut this short and have a table with all the synonyms and misspellings, so that i can avoid all this writeup , so will need something like:

select query from HR_Health_Logs where query REGEXP '**HAVE A TABLE WITH ALL MY SYNONYMS AND MISSPELLINGS SEARCHED HERE**';

Answer 1

SELECT  query
FROM    HR_Health_Logs l, synonym s
WHERE   l.query = s.synonym

Answer 2

How about the ANY function ?

select query from HR_Health_Logs where query REGEXP ANY (SELECT spell FROM misspelled WHERE correct = 'masturbate' ) ;

Answer 3

SELECT query
FROM HR_Health_Logs
WHERE query IN (
  SELECT synonym AS query
  FROM synonyms_table
  WHERE word = 'masturbation'
  UNION
  SELECT misspelling AS query
  FROM misspellings_table
  WHERE word = 'masturbation'
)

Assuming your synonyms and misspellings are in two separate tables. Otherwise you'll only use one of the subqueries and drop the UNION.