Using SOUNDEX to match individual words within Text fields
I have a table which contains a Text fields containing course information.
I am constructing a 'fuzzy search' stored procedure which will match words within this field based on what was entered into a web front end.
The part I am finding difficulty with is matching on mis-spelled words
I thought I could use SOUNDEX for this but it only appears to be finding matches where the first word of the Text field is the word I am looking for.
So, for example, my search value is 'compter course' (note it is mis-spelled)
There are records in my DB which have text field value of 'Computers for beginners' and 'An introduction to computers'
My SOUNDEX query partly does the job but only the first record is matched :(
Is there a way to use SOUNDEX across all words in the text field or is ther another approach I can look at?
I am using SQL 2005
Thanks
I am constructing a 'fuzzy search' stored procedure which will match words within this field based on what was entered into a web front end.
The part I am finding difficulty with is matching on mis-spelled words
I thought I could use SOUNDEX for this but it only appears to be finding matches where the first word of the Text field is the word I am looking for.
So, for example, my search value is 'compter course' (note it is mis-spelled)
There are records in my DB which have text field value of 'Computers for beginners' and 'An introduction to computers'
My SOUNDEX query partly does the job but only the first record is matched :(
Is there a way to use SOUNDEX across all words in the text field or is ther another approach I can look at?
I am using SQL 2005
Thanks
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<you cannot use soundex efficiently for this.>>
Agreed. I would also ask: why use soundex at all.
Agreed. I would also ask: why use soundex at all.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
My SP uses all the other methods mentioned above already, none of them produce a match on 'compting' except soundex. I already do some tidying up of noise words etc as well, the SP is growing into a bit of a monster.
I agree the design of the DB & application is not perfect but its what I've been given to work with
My SP uses all the other methods mentioned above already, none of them produce a match on 'compting' except soundex. I already do some tidying up of noise words etc as well, the SP is growing into a bit of a monster.
I agree the design of the DB & application is not perfect but its what I've been given to work with
ASKER
Update
Fortunately the data used here is only refreshed every few weeks so Ive created an overnight which splits the words in the search field into separate fields in a new table, then a soundex match is performed on each field in turn.
Hopefully this will catch enough typos to let us escape creating a thesaurus which I really, really want to avoid
Fortunately the data used here is only refreshed every few weeks so Ive created an overnight which splits the words in the search field into separate fields in a new table, then a soundex match is performed on each field in turn.
Hopefully this will catch enough typos to let us escape creating a thesaurus which I really, really want to avoid
just try to look this may help you.. not sure it wil solve your problem or not..
I am looking your problem and give the feedback very soon...