CODEWITHSUNDEEP
mysqlsqlalgorithm
MarathonStudios
MarathonStudios

Reputation: 4321

Search for a value within an input string in a MySQL database

I have a database of job descriptions, and I need to match these descriptions with as many job listings as possible. In my database, I have a primary job title as a key (for example, Aircraft Pilot), and several alternate titles (Jet Pilot, Airliner Captain, etc).

My problem is that with many of the descriptions I have to process, the title includes too much information - a sample title from a listing might be "747 Aircraft Pilot", for example.

While I know I can't get 100% accuracy matching this way, would there be any way for me to match something like "747 Aircraft Pilot" with my description for "Aircraft Pilot" without running a search on each combination of words in the string? Is there an algorithm, for example, that would assign a match percentage between two strings and return all pairs with a certain percentage matching, for example?

Upvotes: 1

Views: 179

Answers (1)

dugokontov
dugokontov

Reputation: 4612

You can use Full-text search function in MySQL. A good tutorial can be found here:

http://devzone.zend.com/article/1304

http://forge.mysql.com/w/images/c/c5/Fulltext.pdf

When you add Fulltext index using

ALTER TABLE jobs ADD FULLTEXT(body, title);

You can do query like this:

mysql> SELECT id, title, MATCH (title,body) AGAINST
    -> ('Aircraft Pilot')
-> AS score
-> FROM jobs WHERE MATCH (title,body) AGAINST
-> ('Aircraft Pilot');
+-----------------------------+------------------+
| id | title                  | score            |
+-----------------------------+------------------+
|  4 | 747 Aircraft Pilot ... | 1.5055546709332  |
|  6 | Aircraft Captain   ... |   1.31140957288  |
+-----------------------------+------------------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions