Using the soundex mysql function with like parameter

Asked

Viewed 1,305 times

1

How can I use the soundex function of mysql as a like parameter

for example I have a table people with the name Poliana Mendes Silva

I want to search with soundex all the "polys" of the bank ignoring the rest of the name

mysql soundex

  • soundex generates a single string for multiple words, not to be used with substring functions like Like. For this kind of thing, better fulltext. Besides, soundex is "tunado" to the English language.

  • PS: You could do something like this, but you would have to make a special field of names recorded with soundex word by word, and create a function to fill this field whenever the data is entered or modified. Probably, doing all this and spending extra space for a system that was designed for the English language will not get better than fulltext anyway.

  • I was able to solve it like this "SELECT * FROM WHERE CLIENTS " . " SOUNDEX( SUBSTRING_INDEX(NOME_COMPLETO, ' , 1) ) = SOUNDEX('PAULA') " . " AND SUBSTRING_INDEX(NOME_COMPLETO, ' ', -1) = :SURNAME"

  • The logic is more or less this, but as you must have noticed, it will be suffered you manage names like "João Augusto Conrado do Amaral Gurgel". As experience counts, but as a solution to a real system, it will give you more work than solution.

2 answers

0


I managed to sort it out like this

"SELECT * FROM Clients WHERE SOUNDEX( SUBSTRING_INDEX(NOME_COMPLETO, ' ', 1) = SOUNDEX('PAULA') AND SUBSTRING_INDEX(NOME_COMPLETO, ' ', -1) = 'LAST NAME'"

-1

You can try it:

select * from clientes
where (soundex(nome) LIKE concat('%',soundex('POLIANA'),'%'))
  • 4

    Trying it can, but it doesn’t work. If it’s Maria Poliana, change the initial. In addition, soundex('Poliana') will have a zero end, and in Poliana Mendes this zero end disappears. It doesn’t work like this.

  • To further clarify the problem: select soundex('POLIANA') = P450 and select soundex('POLIANA MENDES') = P4532, ie, like will not find the match. This happens because soundex has a minimum of 4 characters

Browser other questions tagged mysql soundex

You are not signed in. Login or sign up in order to post.