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

Groups > comp.lang.php > #16663 > unrolled thread

Fuzzy searching inside a MySQL DB

Back to article view | Back to comp.lang.php

Contents

  Fuzzy searching inside a MySQL DB bit-naughty@hotmail.com - 2016-03-17 10:16 -0700
    Re: Fuzzy searching inside a MySQL DB Jerry Stuckle <jstucklex@attglobal.net> - 2016-03-17 13:34 -0400
    Re: Fuzzy searching inside a MySQL DB "R.Wieser" <address@not.available> - 2016-03-17 18:37 +0100
    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 Dr Eberhard Lisse <nospam@lisse.NA> - 2016-03-22 16:35 +0200
    Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-17 16:27 -0400
      Re: Fuzzy searching inside a MySQL DB bit-naughty@hotmail.com - 2016-03-21 11:09 -0700
        Re: Fuzzy searching inside a MySQL DB Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-03-21 14:22 -0400
        Re: Fuzzy searching inside a MySQL DB "R.Wieser" <address@not.available> - 2016-03-21 19:24 +0100
        Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-21 14:30 -0400
          Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-21 23:04 +0100
            Re: Fuzzy searching inside a MySQL DB bit-naughty@hotmail.com - 2016-03-22 00:31 -0700
              Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-22 10:15 -0400
                Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-22 21:53 +0100
                  Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-23 13:26 -0400
                    Re: Fuzzy searching inside a MySQL DB bit-naughty@hotmail.com - 2016-03-25 00:03 -0700
                      Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-25 11:52 -0400
                        Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-25 19:26 +0100
                          Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-25 15:51 -0400
                            Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-25 21:52 +0100
                            Re: Fuzzy searching inside a MySQL DB Jerry Stuckle <jstucklex@attglobal.net> - 2016-03-25 20:20 -0400
                      Re: Fuzzy searching inside a MySQL DB Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-03-25 12:11 -0400
                    Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-25 08:49 +0100
                    Re: Fuzzy searching inside a MySQL DB gordonb.3vp6x@burditt.org (Gordon Burditt) - 2016-03-26 13:44 -0500
                      Re: Fuzzy searching inside a MySQL DB bit-naughty@hotmail.com - 2016-03-27 12:42 -0700
                        Re: Fuzzy searching inside a MySQL DB Mike Mellen <mmellen@intellikey.com> - 2016-03-28 15:02 -0400
                      Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-28 01:48 +0200
              Re: Fuzzy searching inside a MySQL DB Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-22 21:50 +0100

Page 1 of 2  [1] 2  Next page →

#16663 — Fuzzy searching inside a MySQL DB

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?


The only thing I can think of is to think of various word combinations for "sunflower", and then do a SELECT for *each* of them on the MYSQL DB - but this sounds pretty nonsensical, and there would be huge overhead *anyway* - but am I barking up the right tree with this line of thought? If not, then how do I do it?

I've Googled and found pages with the *algorithms* to do fuzzy matching, some giving away their code, but noone seemed to address the problem of having the word *inside MySQL*!!


Thanks.

[toc] | [next] | [standalone]

#16664

On 3/17/2016 1:16 PM, 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?
> 
> 
> The only thing I can think of is to think of various word combinations for "sunflower", and then do a SELECT for *each* of them on the MYSQL DB - but this sounds pretty nonsensical, and there would be huge overhead *anyway* - but am I barking up the right tree with this line of thought? If not, then how do I do it?
> 
> I've Googled and found pages with the *algorithms* to do fuzzy matching, some giving away their code, but noone seemed to address the problem of having the word *inside MySQL*!!
> 
> 
> Thanks.
> 

This has nothing to do with PHP.  Try comp.databases.mysql.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

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

#16665

Bit_naughty,

> what are my options to do this?

You almost gave one possible answer yourself: Create another column in your
database holding the "fuzzy" spelling of the origional column, and search on
that one.

Yes, that does cost you another column.  The upside is that the searching
can again be done with a standard, fast SQL query.

Regards,
Rudy Wieser


-- Origional message:
<bit-naughty@hotmail.com> schreef in berichtnieuws
f6d322a6-47cc-466e-97a7-03c065bf24ec@googlegroups.com...
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?


The only thing I can think of is to think of various word combinations for
"sunflower", and then do a SELECT for *each* of them on the MYSQL DB - but
this sounds pretty nonsensical, and there would be huge overhead *anyway* -
but am I barking up the right tree with this line of thought? If not, then
how do I do it?

I've Googled and found pages with the *algorithms* to do fuzzy matching,
some giving away their code, but noone seemed to address the problem of
having the word *inside MySQL*!!


Thanks.

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

#16666

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] | [prev] | [next] | [standalone]

#16676

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] | [next] | [standalone]

#16667

On 3/17/2016 1:16 PM, 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?
>
>
> The only thing I can think of is to think of various word combinations for "sunflower", and then do a SELECT for *each* of them on the MYSQL DB - but this sounds pretty nonsensical, and there would be huge overhead *anyway* - but am I barking up the right tree with this line of thought? If not, then how do I do it?
>
> I've Googled and found pages with the *algorithms* to do fuzzy matching, some giving away their code, but noone seemed to address the problem of having the word *inside MySQL*!!
>
>
> Thanks.
>

Would the Soundex() function do what you need? As R.Wieser suggested, 
add a "fuzzy" column that contains the Soundex version of the searchable 
column. When the user enters a search string, generate the Soundex code 
for it, and use that to search the "fuzzy" column.

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

#16668

On Friday, March 18, 2016 at 1:57:30 AM UTC+5:30, Mike Mellen wrote:

> Would the Soundex() function do what you need? 

.....sorry, what's Soundex exactly? It definitely *sounds* familiar, I'm sure I've read of it somewhere......?

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

#16669

On Monday March 21 2016 14:09, in comp.lang.php, "bit-naughty@hotmail.com"
<bit-naughty@hotmail.com> wrote:

> On Friday, March 18, 2016 at 1:57:30 AM UTC+5:30, Mike Mellen wrote:
> 
>> Would the Soundex() function do what you need?
> 
> .....sorry, what's Soundex exactly? It definitely *sounds* familiar, I'm
> sure I've read of it somewhere......?

http://lmgtfy.com/?q=Soundex

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

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

#16670

bit-naughty,

https://en.wikipedia.org/wiki/Soundex

... which is the first result when I googeled for "soundex" (hint, hint,
hint).

Regards,
Rudy Wieser


-- Origional message:
<bit-naughty@hotmail.com> schreef in berichtnieuws
3171cc72-16d1-4b88-bcd9-a3a3b4127bc8@googlegroups.com...
> On Friday, March 18, 2016 at 1:57:30 AM UTC+5:30, Mike Mellen wrote:
>
> > Would the Soundex() function do what you need?
>
> .....sorry, what's Soundex exactly? It definitely *sounds* familiar, I'm
sure I've read of it somewhere......?

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

#16671

On 3/21/2016 2:09 PM, bit-naughty@hotmail.com wrote:
> On Friday, March 18, 2016 at 1:57:30 AM UTC+5:30, Mike Mellen wrote:
>
>> Would the Soundex() function do what you need?
>
> .....sorry, what's Soundex exactly? It definitely *sounds* familiar, I'm sure I've read of it somewhere......?
>
Soundex is an algorithm that reduces a word or phrase to a string of 
letters and numbers that represent its phonetic sound. Similar sounding 
words (there, their, and they're, for example) all produce the same 
soundex string. mySQL has a soundex() function:

select last_name,emp_ID from employees where soundex(last_name) = 
soundex(search_string);

would find all employees whose last names "sound like" the search 
string. For example, entering "Smith" as the search string would match 
Smith, Smithe, Smyth, etc.

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

#16672

Mike Mellen wrote:

> On 3/21/2016 2:09 PM, bit-naughty@hotmail.com wrote:
>> On Friday, March 18, 2016 at 1:57:30 AM UTC+5:30, Mike Mellen wrote:
>>> Would the Soundex() function do what you need?
>>
>> .....sorry, what's Soundex exactly? It definitely *sounds* familiar, I'm
>> sure I've read of it somewhere......?
>
> Soundex is an algorithm that reduces a word or phrase to a string of
> letters and numbers that represent its phonetic sound. Similar sounding
> words (there, their, and they're, for example) all produce the same
> soundex string. mySQL has a soundex() function:
> 
> select last_name,emp_ID from employees where soundex(last_name) =
> soundex(search_string);
> 
> would find all employees whose last names "sound like" the search
> string. For example, entering "Smith" as the search string would match
> Smith, Smithe, Smyth, etc.

Note that Soundex was designed for *English* pronunciation and therefore 
works best with English words (although not-too-bad results with another 
Germanic language, German, have been reported; doubtless due to the 
similarities in *some* words – German is my native language).  In any case, 
it only is designed for *proper* words that can be *pronounced*, not 
something like “sunflow3r” (example from the OP).  I think it is a happy 
coincidence that soundex('sunflow3r') returns 'S514' the same as 
soundex('sunflower').  For example, soundex('sunf10w3r') – a common 
Leetspeak modification – already returns 'S516'.  soundex('$unf10w3r') then 
returns 'U516' because Soundex simply *ignores* the "unpronounceable" “$” 
the same as it *ignored* the “3” in the previous example and in the proper 
word the “e” before the “r” is not emphasized (UK: [ə]; US: [ɚ]).

metaphone() is more accurate for English words than soundex(), but worse 
with other natural languages (which is why there are separate 
implementations of algorithms for Brazilian Portuguese, Spanish, Bangla, 
Amharic, Russian, and German).  And it still would not calculate “sunflow3r” 
as similar to “sunflower” because the former cannot be pronounced similarly 
or at all.  metaphone('sunflow3r') returns 'SNFLR', while 
metaphone('sunflower') returns 'SNFLWR'.

For that kind of comparison, you should use levenshtein() or similar_text() 
instead.


levenshtein() implements the Levenshtein distance algorithm: The L. distance 
between two strings is the minimum number of additions, replacements, or 
deletions that are required to transform the first string into the second 
one.  Thus, the Levenshtein distance between “sunflow3r” and “sunflower” is 
1, and between “sunburn” and “sunflower” is 6:

  0. sunburn
  1. sunfurn     (replacement)
  2. sunflrn     (replacement)
  3. sunflorn    (addition)
  4. sunflowrn   (addition)
  5. sunflowern  (addition)
  6. sunflower   (deletion)

IOW, “sunflow3r” is more similar to “sunflower” than “sunburn” because you 
only need to replace one character to transform the first to the second one.


similar_text() is described, but not well referred to, in the PHP manual.
A summary of the underlying algorithm can be found via

<https://books.google.com/books?id=e7D-mITABmEC&pg=PT501&lpg=PT501&dq=%22ian+oliver%22+%22programming+classics%22+similar_text&source=bl&ots=oavkzNrqVv&sig=1SY1by3i68vvjwo3J-suMv29Af0&hl=hu&ei=jGMfTPKUGN-XOMbWrfsL&sa=X&oi=book_result&ct=result#v=onepage&q=%22ian%20oliver%22%20%22programming%20classics%22%20similar_text&f=false>

(it does not matter if you use books.google.com, .hu, .ch, or whatever) 
which I have found in <http://stackoverflow.com/a/3084791/855543> via a 
Google search for the mentioned book title (so please give that guy an 
upvote, too).

It might be a good idea to run the return values of soundex() or metaphone() 
through levenshtein() or similar_text().  soundex() and metaphone() can only 
give you a canonical representation of the word, while levenshtein() and 
similar_text() can give you a *quantitative* idea *how similar* two strings 
are; in this case then, *how similar* the representations of the words are.  
(The Levensthein distance and Similar Text distance between the 
representations of two exactly similar sounding words is 0.)

  [Note that the functions named above were also listed explicitly in the 
   Study Guide for the Zend PHP 5.3 Certification in the “String functions” 
   section in 2014, and in the Study Guide for the ZCE PHP (5.4) 
   Certification in 2015.  I cannot be sure (because I am not going to buy 
   it), but I think it is likely that they are still listed there in the
   Study Guide for the PHP 5.5-based exam.  So you should know them if you 
   decide to take the exam.]

Also note that the performance of the application is probably improved if 
the comparison algorithm is written in the query language as suggested, if 
necessary as a stored function, instead of in PHP, as then you only have to 
fetch matching records (instead of all of them).  MySQL does not have a 
built-in Levenshtein distance implementation, but this wheel has been 
invented already; STFW.

-- 
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]

#16673

...sorry, I'm a little confused -  do levenshtein() and  similar_text() ship WITH MySQL?

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

#16675

On 3/22/2016 3:31 AM, bit-naughty@hotmail.com wrote:
> ...sorry, I'm a little confused -  do levenshtein() and  similar_text() ship WITH MySQL?
>

No, but there are PHP levenshtein() and similar_text() functions. There 
is a soundex() function in mySQL, which is why I suggested it.

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

#16678

Mike Mellen wrote:

> On 3/22/2016 3:31 AM, bit-naughty@hotmail.com wrote:
>> ...sorry, I'm a little confused -  do levenshtein() and  similar_text()
>> ship WITH MySQL?
> 
> No, but there are PHP levenshtein() and similar_text() functions. There
> is a soundex() function in mySQL, which is why I suggested it.

AISB, from the OP follows that Soundex is not the correct solution here.

-- 
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]

#16679

On 3/22/2016 4:53 PM, Thomas 'PointedEars' Lahn wrote:
> Mike Mellen wrote:
>
>> On 3/22/2016 3:31 AM, bit-naughty@hotmail.com wrote:
>>> ...sorry, I'm a little confused -  do levenshtein() and  similar_text()
>>> ship WITH MySQL?
>>
>> No, but there are PHP levenshtein() and similar_text() functions. There
>> is a soundex() function in mySQL, which is why I suggested it.
>
> AISB, from the OP follows that Soundex is not the correct solution here.
>

 From the OP's minimal description, it sounds like he/she wants to 
search an English language  (suggested by word choice and column name) 
database to match user input with possible typos. There is no mention of 
"leetspeak" anywhere.

If this description is correct (OP - could you please verify?) then 
soundex is a perfectly viable solution.  I've used both soundex and 
metaphone in applications. Metaphone worked well in an application where 
users had to find foreign names when they didn't know the spelling; 
soundex would have done the job just as well. Since soundex in included 
in mySQL, why not use it?

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

#16680

On Thursday, March 24, 2016 at 8:35:32 AM UTC+5:30, Mike Mellen wrote:

> If this description is correct (OP - could you please verify?) 


Yes, it has nothing to do with "leetspeak" - its more like, do you know that rap group NWA - that stands for "Niggaz With Attitude" - so what if someone searches for .... "Niggers with Attitude".....? They have a song called "Fuck Tha Police", so... what if someone searches for "Fuck The Police"....? THAT kind of thing......

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

#16682

On 3/25/2016 3:03 AM, bit-naughty@hotmail.com wrote:
> On Thursday, March 24, 2016 at 8:35:32 AM UTC+5:30, Mike Mellen wrote:
>
>> If this description is correct (OP - could you please verify?)
>
>
> Yes, it has nothing to do with "leetspeak" - its more like, do you know that rap group NWA - that stands for "Niggaz With Attitude" - so what if someone searches for .... "Niggers with Attitude".....? They have a song called "Fuck Tha Police", so... what if someone searches for "Fuck The Police"....? THAT kind of thing......
>

You can test it for yourself.  From the mySQL command line, just enter

select soundex("expr1");
select soundex("expr2");

where expr1 and expr2 are the two strings you want to compare; note they 
should be inside quotes. You can see the soundex codes that are 
generated.  If you don't consistently get matching codes for typical 
search strings your users might enter, you could then use the PHP 
levenshtein() or similar_text() functions on the soundex codes to 
determine how "close" the matches are, and use that result to determine 
if you have a match. It won't be perfect, but only you can decide what 
is "close enough".

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

#16684

Mike Mellen wrote:

> On 3/25/2016 3:03 AM, bit-naughty@hotmail.com wrote:
>> On Thursday, March 24, 2016 at 8:35:32 AM UTC+5:30, Mike Mellen wrote:
>>> If this description is correct (OP - could you please verify?)
>> Yes, it has nothing to do with "leetspeak" - its more like, do you know
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> that rap group NWA - that stands for "Niggaz With Attitude" - so what if
>> someone searches for .... "Niggers with Attitude".....? They have a song
>> called "Fuck Tha Police", so... what if someone searches for "Fuck The
>> Police"....? THAT kind of thing......

Your example (OP) *definitely* had leetspeak in it, so by now you have 
mislead us *and* lied to us.  I am not surprised of that.
 
> You can test it for yourself.  From the mySQL command line, just enter
> 
> select soundex("expr1");
> select soundex("expr2");
> 
> where expr1 and expr2 are the two strings you want to compare; note they
> should be inside quotes. You can see the soundex codes that are
> generated.  If you don't consistently get matching codes for typical
> search strings your users might enter, you could then use the PHP
> levenshtein() or similar_text() functions on the soundex codes to
> determine how "close" the matches are, and use that result to determine
> if you have a match. It won't be perfect, but only you can decide what
> is "close enough".

You are not paying attention.

As I showed, the Soundex strings of “expr1” and “expr2” are identical 
because *Soundex* *ignores* *non-letters*:

--------------------------------------------------------------------------
$ mysql -u root -p <<<'SELECT SOUNDEX("good4you"), SOUNDEX("2good2Btrue"), 
SOUNDEX("goodBtrue"), SOUNDEX("togoodtobetrue")\G'
Enter password: 
*************************** 1. row ***************************
      SOUNDEX("good4you"): G300
   SOUNDEX("2good2Btrue"): G3136
     SOUNDEX("goodBtrue"): G3136
SOUNDEX("togoodtobetrue"): T23136
-------------------------------------------------------------------------

Notice that the first, second, and third Soundex value begin with “G”, and 
the fourth one with “T”.  Notice that the second and third value are 
*identical*.  Notice that the second, third, and fourth value end in “3136”; 
this comes from

      G    3      00
      g oo d 4you

      G    3     1   3 6
    2 g oo d 2   B   t r ue

      G    3     1   3 6
      g oo d     B   t r ue

  T   2    3     1   3 6
  t o g oo d t o b e t r ue

<https://en.wikipedia.org/wiki/Soundex#American_Soundex>

levensthein() and similar_text() *work* *differently*.

-- 
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]

#16685

On 3/25/2016 2:26 PM, Thomas 'PointedEars' Lahn wrote:
>
> You are not paying attention.
>
> As I showed, the Soundex strings of “expr1” and “expr2” are identical
> because *Soundex* *ignores* *non-letters*:

I am quite familiar with how all of the referenced algorithms work, and 
where they are most useful, having written a number of applications 
involving name/word search.

I have been responding to the OP's original question (which did not 
involve any lies or misleading statements), and offering a possible 
solution. You keep trying to make the question more complicated than it 
really is. You say the sunflower/sunflow3r confusion is leetspeak; I say 
it's a typo caused by pressing the wrong adjacent key (3 vs e) when 
entering a string.

To the OP: Only you know what kind of data you'll be storing in your 
database, what kind of mistakes you expect from your users, and how 
important it is to find an exact match or just a couple of possible 
matches.  I suggested the soundex algorithm as a starting point because 
mySQL has a built-in function for it, so you can do some quick 
evaluations to determine if it does the job for you. If it works, great, 
you have a lot less work to do. If it doesn't, maybe you can supply some 
more details about your application if you'd like more assistance.

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

#16686

Mike Mellen wrote:

> On 3/25/2016 2:26 PM, Thomas 'PointedEars' Lahn wrote:
>> You are not paying attention.
>>
>> As I showed, the Soundex strings of “expr1” and “expr2” are identical
>> because *Soundex* *ignores* *non-letters*:
> 
> I am quite familiar with how all of the referenced algorithms work, and
> where they are most useful, having written a number of applications
> involving name/word search.

So much the worse that you would recommend it here.
 
> I have been responding to the OP's original question (which did not
> involve any lies or misleading statements),

AISB, you are not paying attention.

> and offering a possible solution. You keep trying to make the question
> more complicated than it really is. You say the sunflower/sunflow3r
> confusion is leetspeak; I say it's a typo caused by pressing the wrong
> adjacent key (3 vs e) when entering a string.

Wishful thinking.  It would be a really strange typo that, if it had not 
been made, would have resulted in a question containing

“if someone types in "sunflower" into the box, I would like it to match 
"sunflower", if it's stored in the DB”

under the Subject “*Fuzzy searching* inside a MySQL DB” (emphasis mine).

-- 
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]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.php

csiph-web