Re: Fuzzy searching inside a MySQL DB

Followups set to comp.databases.mysql

On Thursday March 17 2016 13:16, in comp.lang.php, "bit-naughty_at_hotmail.com" <bit-naughty_at_hotmail.com> wrote:

> Hi,
> If I have a search box on my page which will search through data stored in a
> MySQL DB, if someone types in "sunflower" into the box, I would like it to
> match "sunflow3r", if it's stored in the DB, say under the field
> "typeofflower" - what are my options to do this?

MySQL supports two forms of pattern matching: the standard SQL "LIKE" verb, and the MySQL-specific "REGEXP" verb.

Your search will take a bit of preprocessing to use either of these verbs. You will have to, before passing the select statement to the sql parser, substitute obvious errors with search wildcard values, then use a pattern-matching search.

For example: If your user enters "sunflow3r" instead of "sunflower", you would substitute the obvious error with a wildcard character, resulting in   "sunflow.r" or "sunflow%r" for LIKE processing, or   "sunflow.r" or "sunflow*r" for REGEXP processing.

You would then issue your SELECT:
  SELECT * FROM flowers WHERE typeofflower LIKE "sunflow.r"; or
  SELECT * FROM flowers WHERE typeofflower REGEXP "sunflow.r";

"LIKE" searches are fairly limited, but standard in all forms of SQL "REGEXP" searches are more versatile, but not supported (AFAIK) outside of MySQL and it's variants.

HTH

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request