SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    soundex() question

    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
    Code:
    SELECT name
    FROM animals
    WHERE soundex(name) = soundex("beard")
    and let's assume I have the following table
    | name |
    Bird one

    thank you

  2. #2
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    leo d.

  3. #3
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    write my own soundex() function? lool

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    id | name
    -------
    1  | Bird one
    2  | furry tiger cub
    Code:
    id | soundex
    -------
    1  | B630
    1  | O500
    2  | F600
    2  | T260
    2  | C100
    Code:
    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'))

  7. #7
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you
    I think this is the only way..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •