Question 21456 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Limit the number of characters in LIKE clause?

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

Tags:

2

Answers


  1. 0

    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}$';
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search