Menu
I am trying to implement a first and last name search using full-text search and SOUNDEX (in case if the name is misspelled).
I was trying to do something like
SELECT *
FROM employees
WHERE
MATCH SOUNDEX(first_name, last_name) AGAINST SOUNDEX('John 1969 Ivan')
but this is not a valid syntax.
What I want to achieve, is that when a user types for example "Jon Ivan", the columns
first_name | last_name
----------------------
John Ivan
would match.
Thank you in advance!
763
MySQL has support for full-text indexing and searching: A full-text index in MySQL is an index of type FULLTEXT . Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR , VARCHAR , or TEXT columns.
The basic query format of full-text searches in MySQL should be similar to the following: SELECT * FROM table WHERE MATCH(column) AGAINST(“string” IN NATURAL LANGUAGE MODE); When MATCH() is used together with a WHERE clause, the rows are automatically sorted by the highest relevance first.
By default, the search is case-insensitive. To perform a case-sensitive full-text search, use a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.
SOUNDEX() function in MySQL is used to return a phonetic representation of a string. The phonetic represents the way the string will sound. The SOUNDEX function helps to compare words that are spelled differently, but sound alike in English. Syntax : SOUNDEX(str)
MySQL tends to fall short when it comes to text searches, fuzzy searches, misspellings, etc. I highly recommend an indexing solution like Solr. Solr supports four different types of phonetic searches: Soundex, RefinedSoundex, Metaphone, and DoubleMetaphone. It has geospatial searching and misspelling searches along with butterfly/butterflies searching. I think you will REALLY be happy with the results you get. It is lightening fast compared to MySQL
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
