Searching multiple fields for any/all words from a search string.

[LarrySteele]

We have an application that includes a people searcher. As people enter text, it searches four different fields (first name, last name, ID, email address) for the text entered.

This works awesomely.

If I enter "Lawrence", it will find me, along with anyone who has "Lawrence" in their first name and/or last name. Likewise, if I enter "Steele", it will find me along with everyone else with "Steele" as last name.

The problem: there are a lot of people with Lawrence in their first name and a lot of people with Steele in their last name. It's worse for the Smiths, Joneses, and Patels.

Intuitively, people try a narrow it down by typing "Lawrence Steele" since there's only one of me. Ah, but therein lies the rub. There are no records where first name is "Lawrence Steele" and no records where last name is "Lawrence Steele". Accordingly, I'm not found.

Here are some challenges:

1. Cannot assume users are entering FIRST_NAME {space} LAST_NAME. They could be searching for a person who has two first names or two last names. Or they could be searching in reverse: LAST_NAME {space} FIRST_NAME. Since text entered could be any of the four fields, they could enter EMAIL_ADDRESS {space} ID. The point is that we cannot assume what pieces of the person search they're sending in this one text field, we just need to be able to accept it, and return best match.

Oh, and it has to perform this live - so as people type, the search has to continually refine records returned.

As I said in the beginning, we have a functioning search operation - it returns records as long as full string is compared against each of the four fields. The wheels fall off the wagon when spaces enter the picture.

Any suggestions?

Thanks in advance.

Larry

 

[Andrzejek]

Why don’t you detect if user typed a Space(s) between the words, search for each word separately, and then Inner Join both outcomes.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[LarrySteele]

Andy, that's an interesting idea. I can see potential challenges but it gives me an approach, which is more than I had when I posted the question.

If I can get this to work, I'll post the results. Thanks for the suggestion.