Using Soundex in an Index or Query for Searching

[MoGrace]

I haven't had a reason to try Soundex() in an index or query before so am not sure how this might work if a user tries to do a search but doesn't know how to spell the name.

I have an xdialog that does a progressive search matching on 4 different fields: LASTNAME, FIRSTNAME, SSN & DOB as the user types. Soundex wouldn't help for SSN or DOB. But what I am finding is that if the user types in 'zeig' in an attempt to find ziegler - he isn't going to find it because he spelled it wrong. And if the user who input the record spelled it wrong, typing zieg won't find it either.

I could use a pointer in the right direction as to how to modify my script to use this function on a partial entry - if that's possible.

I am using this keylist_build now:

vKey_rl_def_orig = "kl=tempmemb,{keylist_build("H=.05,1:18[Lname],2:15[Fname],3:3[Mi],4:10[Dob],5:12[Ssn],6:12[Memb_Id]",''+KEYLINK,left(''+lname,18),left(''+fname,15),left(''+mi,3),left(''+dob,10),left(''+ssn,12),left(''+memb_id,12))}{left(dtoc(dob),len([varC->type_in])) = [varC->type_in] .or. left(lname,len([varC->type_in])) = [varC->type_in] .or. left(fname,len([varC->type_in])) = [varC->type_in] .or. left(ssn,len([varC->type_in])) = [varC->type_in] )}"

[Stan Mathews]

Re: Using Soundex in an Index or Query for Searching

I've never had occasion to use it in an index either but the docs say it works in an index to find "close" spellings.

You typically use SOUNDEX() in index expressions and filter expressions to search for records when you are not sure of the correct spelling of the value you want to find.

[MoGrace]

Re: Using Soundex in an Index or Query for Searching

For my example, I did check that zieg and zeig returned the same value, so that's a start.

[Ted Giles]

Re: Using Soundex in an Index or Query for Searching

I've used it Robin and it works well but is not infallible. You may get the odd spurious mismatch, but that is largely academic.

[MoGrace]

Re: Using Soundex in an Index or Query for Searching

In the filter section for the keylist_build above - how would I include soundex() for this section?

left(lname,len([varC->type_in])) = [varC->type_in]

[Stan Mathews]

Re: Using Soundex in an Index or Query for Searching

Just guessing


soundex(lname) = soundex([varC->type_in])

[Ted Giles]

Re: Using Soundex in an Index or Query for Searching

One thing I have had to do is concatenate fields before running soundex.
Looks like you might be able to do similar.
Keylist looks a bit complex for my simple mind.
If all you want to do is suggest possible matches on name, then soundex the name less any spaces and show the rest of the data for the user to do their selection. Then it's up to their power of recognition.

[MoGrace]

Re: Using Soundex in an Index or Query for Searching

Well Soundex() does work - sort of. It helps to narrow the list but returns many non-matches too. Eg. "julee" finds Julie and Julian but also Joel which hardly sounds alike but do all have the same soundex value of J400.

[MoGrace]

Re: Using Soundex in an Index or Query for Searching

Since this is a temporary table, I added 2 fields with calc field rules to hold the soundex value for each of the lname and fname fields. Now my search on this table can match those fields to the soundex value of what the user types into the search variable. If the user types in "mclan" it will find both McClane and McClain and only a couple of others that don't really match the Lname or Fname fields. However the user must type in enough letters to get a soundex value that matches. So if "zigler" is typed, ziegler and zeigler is found, but if a partial entry like "zig" is entered, it won't find anything. I think this will probably accomplish what I was looking to do - which was to find a name even it was misspelled.

Using my new fields in the query filter:

LNAMEX = soundex([varC->type_in]) .or. FNAMEX = soundex([varC->type_in])

I also added indexes for the new fields, though I am not sure that is needed but might use LQO in the search.

And the temp table is only updated automatically when new records are added to the 'live' table. IOW not when the user corrects a record. There is an option to rebuild the table manually too, so if the users want to see their changes in the search list, they can.

[Ted Giles]

Re: Using Soundex in an Index or Query for Searching

And another thing to add to your repertoire.

Try [Difference()] categorises the results (1-4) where 4 is a good match, 1 is a very lose match using Soundex.

Might simplify things and get it a bit more accurate.

I work in some government places where a search of, say %billy% is used. If there is no result, the new record is for a guy named %billy%.
Cut and paste at you peril!

[MoGrace]

Re: Using Soundex in an Index or Query for Searching

That's a function I have not looked at before. I just don't know how to work that into my query without some sort of case statement. Also this is an xdialog. If it was a form search, maybe...