CODEWITHSUNDEEP
mysqlfull-text-search
Rick Riensche
Rick Riensche

Reputation: 1099

MySQL FULLTEXT search for words at beginning or end of string

Posting this one so I can perhaps save some other poor sap from a few hours of searching, or better yet, get a better answer from someone smarter than I in the realm of MySQL. ;-)

How, when searching MySQL using a FULLTEXT index, can I specify that I only want matches that start with or end with a particular string?

In essence, I want the behavior of 

SELECT `idColumn` FROM `someTable` WHERE `textColumn` LIKE '%foo';

A simple index and the query above won't do, because MySQL won't use the index for queries that begin with a wildcard. So I added a FULLTEXT index and started querying with something like:

SELECT `idColumn` FROM `someTable` WHERE MATCH(`textColumn`) AGAINST ('foo');

which gets close, but matches 'foo' anywhere in textColumn.

EDIT: This is an InnoDB table on MySQL 5.6.1

How can I restrict that to match ONLY at the end (or start)?

Upvotes: 4

Views: 6420

Answers (1)

Rick Riensche
Rick Riensche

Reputation: 1099

Best answer I have come up with so far (inspired in part by https://stackoverflow.com/a/6289012/977046) is to use a HAVING clause to filter my fulltext results, a la:

SELECT `idColumn` FROM `someTable` 
WHERE MATCH(`textColumn`) AGAINST ('foo')
HAVING `textColumn` LIKE ('%foo');

Upvotes: 9

Related Questions