My database has 1 field for "name", instead of a f_name, l_name set. Users of the form type all kinds of stuff in there, like "Mr. John F. Doe" and "Sir Walter T Scott III". This makes searching difficult for me. Also, there is another field for their spouse, called "spouse_name" and it's got the same thing going on.
I'd like to search both 'name' and 'spouse_name' and come up with good results. The current query I'm using is:
The search works all right, but some things get in the way. For example, a search for "John Doe" will not find the record "John J. Doe".
I performed a full text search on the name and spouse_name fields, then tried this:
And that turned up thousands of results. The same search for "John Doe" finds every John and every Doe in either field.
I'm not sure what to do next, so your suggestions are much appreciated!