MySQL: Limit the number of characters in LIKE clause?-CodePudding

I'm using this query in my autocomplete feature:

SELECT description FROM my_table WHERE LIKE "%keyword%"

But this query returns the entire content of the field which is sometimes too long.
Is it possible to limit the number of characters before and after "keyword" ?

CodePudding user response：

I suggest using MySQL's REGEXP operator here. For example, to accept a maximum of 10 characters before and after keyword, you could use:

SELECT description
FROM my_table
WHERE col REGEXP '^.{0,10}keyword.{0,10}$';

Note that if you intend to match keyword as a standalone word, you may want to surround it by word boundaries in the regex pattern:

SELECT description
FROM my_table
WHERE col REGEXP '^.{0,10}\\bkeyword\\b.{0,10}$';

CodePudding user response：

To show for example 5 characters before and after you word you can do it using RIGHT, LEFT and SUBSTRING_INDEX

select description, concat(RIGHT(SUBSTRING_INDEX(description, 'keyword', 1),5), 'keyword', LEFT(SUBSTRING_INDEX(description, 'keyword', -1),5) ) as snippet
from my_table
where description like "%keyword%";

Check it here : https://dbfiddle.uk/MZcVJgEL