2

In mysql, I have a WHERE clause that uses LIKE to match a keyword to a string.

WHERE title LIKE CONCAT('%',?,'%')

This almost works, but I running into a problem where titles with multiple words are returning erroneous matches. For example, the keyword "press" would be matched to the title "depression sucks" - which should not happen.

What is the best way to ensure that the keyword matches only if it is at the start of a word?

Thanks (in advance) for your help

Improve this question

5 Answers 5

Reset to default
4

Word boundary markers [[:<:]], [[:>:]] are your friend.

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks - didn't know you could do that. Works great!
1 
WHERE title LIKE CAT(?, ' %')

the % is the wildcard character so if you have it in front and in back, it will look for anything that contains the ?. If you remove the first %, it will be anything that starts with the ?

Improve this answer

3 Comments

thanks for the quick reply. That isn't quite what I'm looking for. I would like to match complete words. So for example, if I have a title "The Quick Brown Fox" and the keyword "Brow" - using the above code would not work.
@Travis: Does putting a space between the search term & the wildcard suit your needs?
@OMG Ponies - almost. But that would not work for the first word in the string.
1

I think you're looking for MATCH rather than LIKE. With proper indexing, MATCH is also much faster.

Here are the basics: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Improve this answer

2 Comments

I am using innoDB - full text searching is not available
Whoops. Oh well, looks like codaddict has a good solution for you!
0

Not familiar with mysql, but I have used REGEXP_LIKE in Oracle... mysql seems to support something similar...

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Then just write the appropriate regex.

Improve this answer

1 Comment

REGEXP in MySQL ensures a table scan - can't use an index
0 
WHERE title LIKE CONCAT('% ', ?, '%') OR title LIKE CONCAT(?, '%')

This way, the query will find the keyword if it's the beginning of a word or the beginning of a title. So, if the keyword is 'cat', it will find the movies:

catwoman
dogs and cats

but not a movie like:

concatenation
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.