MySQL FULLTEXT search for words at beginning or end of string

Question

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)?

score 7 · Accepted Answer · edited May 23 '17 at 10:32

7

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');

edited May 23 '17 at 10:32

Community

1
1

answered Dec 16 '13 at 21:13

Rick Riensche

1,050
1
12
25

awsome the logic worked for me to fetch startwith rows also – Hari Feb 26 '18 at 09:27
Hi @Rick can please answer these questions https://stackoverflow.com/questions/66469171/mysql-match-against-not-search-on-single-string – Sumit patel Mar 04 '21 at 06:12

MySQL FULLTEXT search for words at beginning or end of string

1 Answers1