I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for.

I initially thought that I could do this with locate() to get the index of the string minus ~10 characters and then substring around 200 characters after but this only works if there is a document that includes these words consecutively. For example:

SELECT  id,created_at, modified_at,
        SUBSTRING(content, (locate('pto request', content))) as excerpt
FROM    search_index
WHERE   MATCH (name,content) AGAINST ('pto request' IN BOOLEAN MODE)

The match clause finds documents that contains the words pto and request that may not be consecutive but the the locate function requires the exact string "pto request" to be somewhere in the document, if it isn't, an excerpt is not generated.

My first thought was to check for 0, if 0 locate didn't match anything so remove a word and try again and keep doing so until a locate finds a match, but this could get really messy. Is there a better way?

1 Answer 1


If you want exactly the 2 words, do

... AGAINST('"pto request"' IN BOOLEAN MODE)

If you want some context:

              GREATEST(1, LOCATE('pto request', content) - 20),
FROM ...
  • This isn't really retrieving a relevant excerpt, just defaulting to 50 if the locate index is 0.
    – 10885338
    Commented Aug 8, 2019 at 17:46
  • @Neil - it still needs the MATCH.
    – Rick James
    Commented Aug 8, 2019 at 22:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.