Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.mysql > #6142 > unrolled thread

Re: Fuzzy searching inside a MySQL DB

Back to article view | Back to comp.databases.mysql

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Fuzzy searching inside a MySQL DB Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-03-17 14:53 -0400
    Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-17 22:13 +0100
    Re: Fuzzy searching inside a MySQL DB Dr Eberhard Lisse <nospam@lisse.NA> - 2016-03-22 16:35 +0200

#6142 — 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@hotmail.com"
<bit-naughty@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

[toc] | [next] | [standalone]

#6143

Lew Pitcher wrote:

> "REGEXP" searches are more versatile, but not supported (AFAIK) outside of
> MySQL and it's variants.

As a quick Google search for “sql regular expression” shows, at least the 
following other SQL implementations have it:

- DB2 for i 7.1 and 7.2 (REGEXP_*(…))
- Firebird ([NOT] SIMILAR TO pattern)
- Oracle/PLSQL (REGEXP_*(…))
- PostgreSQL ([NOT] SIMILAR TO $pattern)

You can also have it in Transact-SQL and SQLite, but you need to hack it 
yourself.

See also: <http://trentrichardson.com/2008/10/23/exploring-various-sql-regex-syntax/> p.

-- 
PointedEars
Zend Certified PHP Engineer 
<http://www.zend.com/en/yellow-pages/ZEND024953> | Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#6151

And then there is the Levenshtein distance.


el


On 2016-03-17 20:53, Lew Pitcher wrote:
[...]

> On Thursday March 17 2016 13:16, in comp.lang.php, "bit-naughty@hotmail.com"
> <bit-naughty@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?
[...]

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.mysql

csiph-web