Improve search result by broadening matches

Question

I am trying to improve search result using PHP. e.g. when product name is ZSX-3185BC user can not find the product when put something like ZSX3185BC. So far i get something like:

$input = '01.10-2010';  
$to_replace = array('.','-');
$clean = str_replace($to_replace, '/',$input);`

I've tried this, but it does not work correctly (not sure, posted in comment without elaboration -ed)

$str = ""; 
$len = strlen($strSearch) - 1; 
// Loop through the search string to check each charecter is acceptable and strip those that are not 
for ($i = 0; $i <= $len; $i++) {
    if(preg_match("/\w|-| /",$strSearch[$i])) {
        $str = $str . $strSearch[$i]; 
    }
}

would you like to get the search results replacing special charaters directly in mysql query? — Ravinder Singh, Jun 27 '12 at 10:16
`$str = ""; $len = strlen($strSearch) - 1; // Loop through the search string to check each charecter is acceptable and strip those that are not For ($i = 0; $i <= $len; $i++) { if(preg_match("/\w|-| /",$strSearch[$i])) { $str = $str . $strSearch[$i]; } }` — Nasan Ert, Jun 27 '12 at 13:35

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

You should try to do this kind of fuzzy matching directly in your database query. A common approach to fuzzy string matching is the Levenshtein distance.

MySQL does not have this built in, however, it can be added. See this SO question.

The basic way is to add a stored function to MySQL. There is a Levenshtein implementation as a stored function.

edited May 23 '17 at 12:27

Community

1
1

answered Jun 27 '12 at 10:35

pixelistik

7,541
3
32
42

score -1 · Answer 2 · answered Jun 27 '12 at 10:21

-1

Try this :

SELECT * FROM tablename WHERE replace(fieldname, '-', '') like '%ZSX3185BC%';

answered Jun 27 '12 at 10:21

Ravinder Singh

3,113
6
30
46

This will force a full table scan and cannot use any indices. – Emil Vikström Jun 27 '12 at 10:24
And `SELECT *` is poor practice -- specify columns. I know you don't know columns in this case (not provided), but just don't type the star! – Chris Baker Jun 28 '12 at 04:24
i just gave the example, and he is able to specify the columns he wants. – Ravinder Singh Jun 28 '12 at 06:06

Improve search result by broadening matches

2 Answers2