MySql Regular Expression Select Columns Matching (dynamic)multiple Values within Stored Procedure

Question

I am trying to generate a query where I want to select columns(text) matching multiple values.

eg: I have two columns, id and description. suppose my first row contains description column with value

Google is website and an awesome search engine

, second row with description column value

Amazon website is an awesome eCommerce store

I have created a query

Select * from table_name where description REGEXP 'Website \| Search'

It returns both the rows i.e with both google and amazon, but i want to return only google as i want those rows with both the words website and search also the number of words to be matched is also not fixed, basically the query I am creating is for a search drop down,

All the words that are passed should be present in the column, the order of the words present in the column is not important. If there are other better options besides using regex , please do point out.

Editing: the number of words that are passed are dynamic and not known, the user may pass additional words to be matched against the column. I would be using the query within a stored Procedure

Answer 1

Really don;t think the regex solution is going to be good for you from a performance point of view. Think you should be looking for FULL text searches.

Specifically you need to create a full text index with something like this in the table definition:

create table testTable
(
Id int auto_increment not null,
TextCol varchar(500)
fulltext(TextCol)
); 

Then your query gets easier:

select * from testTable where Match(TextCol) against ('web') 
AND Match(TextCol) against ('server')

Strongly suggest you read the MySQL docs regarding FULLTEXT matching and there are lots of little tricks and features that will be useful in this task (including more efficient ways to run the query above)

Edit: Perhaps Boolean mode will help you to an easy solution like this:

Match(textCol) against ('web+ Server+' in boolean mode)

All you have to do is build the against string so I think this can be done in an SP with out dynamic SQL