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
WNottsCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
http://databases.about.com/od/development/l/aasoundex.htm

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...

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot use soundex efficiently for this.
you might consider using full-text search for this kind of query.
look up
CONTAINS
CONTAINSTABLE
FREETEXTTABLE
in the books online.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<you cannot use soundex efficiently for this.>>
Agreed.  I would also ask: why use soundex at all.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LowfatspreadCommented:
you can use soundex for some limited "misspelled word"  problems but i agree with angelliii and racimo its usage will not be easy ...

you need to filter for noise words and build soundex values for each remaining word and compare those...

full text searching is probably a better solution these days...

however your problem really seems  to stem from allowing a free format input in the firstplace... if your are really trying to allow for searching a course catalogue
why is a simple cross reference / category selection not the simplest approach...

ie type the course to an agreed set of course key words   COMPUTING / BASIC / PROGRAMMING / THEORY / ....

then just let the userr select from the defined set of Keywords...

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WNottsCAuthor Commented:
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
0
WNottsCAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.