MYSQL: Pattern matching

Question

I am trying to write a sql statement that can pull a unique record by comparing it's different variations

For example, I have a unique record of a movie let's say Bad Boys II.

But users do search for;
bad boys 2
badboys2 >
badboys II (2003)
bad boys 2 (2003) etc.

.... LOWER(movie_title) LIKE '%$search_query%'

The above query does not return the unique record i was hoping to get. Any help will be appreciated

This is pattern matching, not pattern recognition. The two are very different. — Fred Foo, Feb 03 '12 at 17:40
Take a look at http://stackoverflow.com/questions/9132382/mysql-query-like-search-and-using-strings-with-symbols — Marcus Adams, Feb 03 '12 at 17:44
If you're using default collations, comparisons are not case sensitive. — Marcus Adams, Feb 03 '12 at 17:48

maya · Answer 1 · 2012-02-03T17:57:16.117

1

You can do the following:
1) Put the words from the string in an array
2) Look for a match with each word:

SELECT * FROM table WHERE LOWER(movie_title) LIKE '%$search_words[$i]%'

Howether, if you search for "bad booys 2" for example with this code you will get all movies which contain the words "bad", "boys" and 2.

edited Feb 03 '12 at 17:57

answered Feb 03 '12 at 17:52

maya

1 Answers1