soundex() question

[zeez]

I'm using soundex() in some queries, however im having a problem with composed words. For example, if i have "bird one" and i compare it to "beard" i don't get a match, but if I compare it to "beard one" it does. Now this makes perfect sense because it takes the whole word at once, but i was wondering, can't we let it check one part of a expression rather than the whole thing without having do break or anything similar?

Sample query

SELECT name
FROM animals
WHERE soundex(name) = soundex("beard")

and let's assume I have the following table
| name |
Bird one

thank you

[ldivinag]

not without rewriting the function.

heck, why dont you just write your own? i needed a DIFF function, the MS SQL had, by mYSQL didnt...

[zeez]

write my own soundex() function? lool

[crmalibu]

You want to only use soundex on the first word of a column? You could use locate() to find the first space, and then use left() to extract the characters leading up to the first space. The result gets fed to soundex()

This wouldn't work if the column might have leading spaces, or if words aren't always seperated by a space character.

You could also maintain seperate columns, or even a seperate table which stores precomputed soundex values for the first N number of words. Searching could be very fast then.

[zeez]

You want to only use soundex on the first word of a column? You could use locate() to find the first space, and then use left() to extract the characters leading up to the first space. The result gets fed to soundex()

Not exactly, what I am looking for is more like a SOUNDEX() LIKE...
I want soundex for any part of the word... I could be making a soundex for
beared or wann and still get "Bird One", without having to write all the parts of the word..

[crmalibu]

Store the precomputed soundex of each individual word in the database and relate it to the record which contains the word. This example isn't properly normalized but you should get the point.

id | name
-------
1  | Bird one
2  | furry tiger cub

id | soundex
-------
1  | B630
1  | O500
2  | F600
2  | T260
2  | C100

SELECT ps.id
     , a.name
  FROM precomputed_soundex ps
 INNER
  JOIN animals a
    ON a.id = ps.id
 WHERE ps.soundex IN (SOUNDEX('beared'), SOUNDEX('wann'))

[zeez]

Thank you
I think this is the only way..