MySQL look for substring in row values?

Question

I want to update search results as the user types.

I'm using MATCH to compare search terms with rows. MATCH is great because it's not case sensitive and it treats accented characters as regular characters.

My problem for this implementation is that it only find results if the user typed the full word.

E.G: If the user types `arbol` MATCH finds all similar rows (árbol, Arbol etc)
however when he just typed `árb` MATCH doesn't find any results.

How should I write the WHERE clause so it shows all occurences whether is in a string or substring?

I believe case sensitivity is dependent on your collation.

Pacerier
– Pacerier

2020-06-13 09:29:01 +00:00
Commented Jun 13, 2020 at 9:29 — Pacerier
– Pacerier, Commented Jun 13, 2020 at 9:29

jka6510 · Accepted Answer · 2012-02-14 21:49:44Z

5

Appending a wildcard (*) to your match string should do what you want.

WHERE MATCH (columnName) AGAINST ("term*");

EDIT: oh, and you'll need:

IN BOOLEAN MODE

as well to use wildcard matching.

answered Feb 14, 2012 at 21:49

jka6510

8365 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JYelton · Accepted Answer · 2012-02-14 21:49:11Z

1

MySQL MATCH uses Full Text searching, which has a default minimum 4-character requirement.

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.1.3, “Server System Variables”.) The default minimum value is four characters; the default maximum is version dependent.

Reference: http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html

answered Feb 14, 2012 at 21:49

JYelton

36.6k28 gold badges139 silver badges196 bronze badges

1 Comment

JYelton Over a year ago

Users would need to type at least four characters or more to get a match. You can edit the variables mentioned to decrease this limit, but it comes with a performance penalty.

Collectives™ on Stack Overflow

MySQL look for substring in row values?

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related