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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.