Find a possible substring in another string with mysql

Question

What is the best way to find a possible substring in another string, using MySQL and/or PHP?

I have script to go trough my list of emails, and use PHP to score, categorize and display them based on my calculated priority. The tricky part is that i have a list of topics in my database i need to check, and i want to match topics from the email-subject against my 'topics' table.

To keep it simple let's say a subject is "LAST REMINDER FOR 2012 FIRST QUARTER DRAWS (OFFICIAL RELEASE).", and in my database i have a matching topic "first quarter draws". What would be the best method to find this record?

Is there a MySQL function for this; or how would you do this? I can only think of manually splitting the subject line into words and run a query against each word and then sort by the id with the most matches; but it seems rather complicated to me and there should be a better solution, or something already solved with a MySQL function.

I already had a look at the "Questions with similar titles" titles here, but the don't help me because they assume that my substring is known.

Ben Rowe · Accepted Answer · 2012-04-02 03:42:49Z

2

A like condition should solve this problem:

SELECT * FROM table WHERE field LIKE '%first quarter draws%'

answered Apr 2, 2012 at 3:42

Ben Rowe

28.8k6 gold badges58 silver badges76 bronze badges

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

2 Comments

iHaveacomputer Over a year ago

Sorry, but this is wrong and does not answer my question. I am aware of the basic mysql sytax; however the main issue here is that i don't know the substring 'first quarter draws' (yet). I only have my subject line "LAST REMINDER FOR 2012 FIRST QUARTER DRAWS (OFFICIAL RELEASE).", and a table with ~6000 records that may, or may not(!) contain the record 'first quarter draws'. How do i find the best match?

Ben Rowe Over a year ago

Sorry, I'm having trouble trying to understand your question. Re-reading I think you're after full text match, and sorting by relevance: devzone.zend.com/26/using-mysql-full-text-searching you could add a FULLTEXT index to your topic field and compare it to your $subject.

JMax · Accepted Answer · 2012-04-02 07:45:51Z

-1

Try this:

if(substr_count( strtolower( $subject ), strtolower( $topic ) ) > 0){
 return true;
}
return false;

edited Apr 2, 2012 at 7:45

JMax

26.7k12 gold badges74 silver badges89 bronze badges

answered Apr 2, 2012 at 3:42

Tales

1,9234 gold badges36 silver badges53 bronze badges

2 Comments

Navarr Over a year ago

This is really unnecessary. It could only be done after fetching everything and there is a function for it: us3.php.net/manual/en/function.strpos.php

iHaveacomputer Over a year ago

Yeah, this wont help either. See my question: "... I already had a look at the "Questions with similar titles" titles here, but the don't help me because they assume that my substring is known."

Collectives™ on Stack Overflow

Find a possible substring in another string with mysql

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related