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

Elemental · Accepted Answer · 2010-10-01 14:36:42Z

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

edited Oct 1, 2010 at 14:36

answered Oct 1, 2010 at 11:09

Elemental

7,5522 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sandy Over a year ago

Elemental thanks for your answer , i will look into FULL text Search, but my major hurdle is that the number of words to be matched against are dynamic it can be any number of words from 0-n, is there any way to go about it, plus the query would be used within a stored procedure.

Elemental Over a year ago

@Sandy Specifically these sql docs might help you dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html - have added bits to my answer too

Michael Mior Over a year ago

Note that you can't just use WHERE MATCH... since MATCH is a function which returns a relevance score. It's not just a boolean value. If you're going with this approach, you'd have to order by the result of MATCH and probably set a threshold.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related