1

Suppose I have a user table. One of a column of the table store for user first name. Also suppose there are there rows in the table. The user first names are as follows : 'Suman','Sumon','Papiya'. Now I want a mysql query if an user search from the table by user first name with 'Suman' then the result will shows two rows one for 'Suman' and another for 'Sumon'.

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Suman Biswas
  • 853
  • 10
  • 19
  • look at the fulltext searches. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html – mayy00 Jun 16 '14 at 18:54
  • Look for *SoundEx* in MySQL – juergen d Jun 16 '14 at 18:56
  • fulltext won't work with "typos". searching for "suman" will return "suman". it won't return "sumon" – Marc B Jun 16 '14 at 18:56
  • Have You tried to add order by name? Cuz when do where firstname like '%sum%' it sorts output data by addition. So You have to add order by firstname to get Sumon after Suman – num8er Jun 16 '14 at 18:57
  • what about using [Levenshtein distance](http://en.wikipedia.org/wiki/Levenshtein_distance) ? – adi rohan Jun 16 '14 at 18:58
  • Look at this.examle from.here http://stackoverflow.com/questions/1588710/mysql-how-to-order-by-relevance-innodb-table I use it when I want to get data by relevance (by.distance) – num8er Jun 16 '14 at 19:03
  • @SumanBiswas your question title is very confusing to get the right direction of the problem you should edit your question accordingly if you need results to match sounds of your column values – M Khalid Junaid Jun 16 '14 at 19:14

1 Answers1

2

You can use soundex it will compare if the sound of values in firstname matches to the sound of provided word

According to docs

When using SOUNDEX(), you should be aware of the following limitations:

  • This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other
    languages may not produce reliable results.

  • This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.

select *
from t
where soundex(firstname)=soundex('Suman')

Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118