5

What is the proper structure for searching within MySql using soundex()? I know how to produce a soundex(): 

select soundex('str');

just not sure how to include this in my query.

Anthon
  • 69,918
  • 32
  • 186
  • 246
jimmytiler
  • 61
  • 1
  • 1
  • 7
  • `where soundex("search_string") = soundex(search_column)` – pala_ Apr 13 '15 at 05:00
  • You can see an example here http://stackoverflow.com/questions/24250267/mysql-search-results-for-similar-sounds/24250438#24250438 – M Khalid Junaid Apr 13 '15 at 05:05
  • I have tried your suggestion pala_ with no luck; it returns no results. I have been searching with the soundex() code, for example, the soundex() code for 'Lewis' is soundex(L200). I'm assuming I search with the soundex() code rather than text or else this defeats the purpose of having a code for soundex() in the first place right. – jimmytiler Apr 13 '15 at 05:13

4 Answers4

7

If you're searching for "lewis" against the name field of people table, you perform this query:

SELECT *
FROM people
WHERE soundex("lewis") = soundex(name);

example here

Pablo Bianchi
  • 1,824
  • 1
  • 26
  • 30
pala_
  • 8,901
  • 1
  • 15
  • 32
  • That worked, thanks. But why is there a soundex() code then, e.g. SELECT SOUNDEX('Lewis')? – jimmytiler Apr 13 '15 at 05:20
  • 1
    it's generating the codes for both the search term, AND the fields it's comparing against. eg `soundex("lewis") = L200` and `soundex("luis") = L200`. So `soundex("lewis") = soundex("luis")` matches, whereas `"lewis" = "luis"` obviously would not. Words that "sound the same" in english should usually have the same code. – pala_ Apr 13 '15 at 05:22
  • Ahh, thanks so much; I've been trying to figure this out for a long time now. – jimmytiler Apr 13 '15 at 05:24
3

Obviously, soundex isn't designed for partials like this (e.g. SELECT SOUNDEX('house cleaning'), SOUNDEX('house'), which would not match), but if you would like to perform a nasty SOUNDEX LIKE, you could

SELECT * FROM tablename WHERE SOUNDEX(column) LIKE CONCAT(SOUNDEX('partial_string'), '%')

You could also do

SELECT * FROM tablename WHERE SOUNDEX(column) LIKE CONCAT(TRIM(TRAILING '0' FROM SOUNDEX('hows ')), '%')

This "kinda" works

Luke Madhanga
  • 6,871
  • 2
  • 43
  • 47
1

This should work

select * from table_name where soundex(column_name) = soundex('word');

This is a good place to read about these:http://www.postgresonline.com/journal/archives/158-Where-is-soundex-and-other-warm-and-fuzzy-string-things.html

thedarkgriffen
  • 394
  • 3
  • 16
0

MySQL has a native way of doing this now!

You can use the syntax SOUNDS LIKE to do a similarity search.

SELECT * FROM table Where SOUNDEX(`field1`) = SOUNDEX(`field2`);

Is equivalent to:

SELECT * FROM table Where `field1` SOUNDS LIKE `field2`;
Aaron Morefield
  • 952
  • 10
  • 18