All geek questions in one place

MySQL is a true LIKE statement

I help a friend build vocabulary grades for the project he is working on. Part of the project is to create a search function. The database is in MySQL, backend in php.

Now starting our simple request was a piece of cake:

SELECT *, ( (CASE WHEN word LIKE '%$query%' THEN 1 ELSE 0 END) + (CASE WHEN defin LIKE '%$query%' THEN 1 ELSE 0 END) ) AS relev FROM dictionary WHERE word LIKE '%$q%' OR defin LIKE '%$q%' ORDER BY relev DESC;

It brought good results; for example, the input of "fire" gave us fire, firemen, firemen, set fire, etc. However, we also need a room for error: we want the error “prnk” to give us prank, prink, as well as pink, or the word “mule”, to also suggest the word “moth”.

It is absolutely amazing that we could not find any information about this. The relevance system is completely superficial, because we do not need actual significance (just a general pointer), but we need something (and therefore we went to the LIKE instruction, and not to the MATCH ... AGAINST instruction, where we did not find anywhere to sort by relevance.)

A database consists of only three things: id, word, defin. Simple as it required complexity (or simplicity).

Thanks to everyone in advance.

+4
source share
5 answers

Try to test if a word sounds like a word in a dictionary, so something like lines:

 SELECT *, ( (CASE WHEN word LIKE '%$query%' THEN 1 ELSE 0 END) + (CASE WHEN defin LIKE '%$query%' THEN 1 ELSE 0 END) + (CASE WHEN LEFT(SOUNDEX(word), 4) = LEFT(SOUNDEX('$query'), 4) THEN 1 ELSE 0 END) + (CASE WHEN LEFT(SOUNDEX(defin), 4) = LEFT(SOUNDEX('$query'), 4) THEN 1 ELSE 0 END) ) AS relev FROM dictionary WHERE word LIKE '%$q%' OR defin LIKE '%$q%' ORDER BY relev DESC;
+4
source

Regarding pranks ...

http://webarto.com/80/did-you-mean-api

 $q = "prnk" $dym = new DYM; $spell = $dym->check($q); if(!empty($spell)){ echo $spell; // prank }

(not quite an API, not very reliable, but it works in less than 0.5 s)

For the mule / mole part, try finding a Levenshtein implementation for SQL ...

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#552 (link does not work, but Google)

http://php.net/manual/en/function.levenshtein.php

+4
source

Take a look at this: Soundex function (sounds like) in mysql

0
source

In addition to Soundex and Levenshtein, you can also watch Metaphone or a double metaphone, although the latter do not have built-in support in MySQL. PHP supports it, but see metaphone - and there are several implementations of two metaphones floating around (i.e. http://swoodbridge.com/DoubleMetaPhone/ ).

0
source

Source: https://habr.com/ru/post/1346141/

More articles:


All Articles