Performing a search on first and last name columns with a single search string that has more than 2 words

Question

I have a query which currently takes a single user supplied search string and tries to search against a table which contains a separate column for firstName and lastName. If the search string has a space in it, the query it runs is essentially like this:

SELECT * FROM table
WHERE table.firstName LIKE @firstName + '%'
AND table.lastName LIKE @lastName + '%'

We don't need to concern ourselves with the case of there being no spaces in the search string.

The general case is pretty simple - split the search string on a space, the first part is the first name, the second part is the last name. So, "Bob Smith" becomes

@firstName = "Bob", @lastName = "Smith"

What I'm hung up on is how to handle cases where the name is more than two words. Situations like

table.firstName        table.lastName
---------------        --------------
Bob                    van Smith
Billy Bob              Smith
Bob                    van der Smith  
Billy Bob              van der Smith

And so on. Right now we split on the first space, so the first example, "Bob van Smith" will work because it breaks into

@firstName: "Bob", @lastName: "van Smith" 

But, this does not catch the second case "Billy Bob Smith" since it splits into

@firstName: "Billy", @lastName: "Bob Smith"

The current set up will also work on the third test case, since it splits into

@firstName: "Bob", @lastName: "van der Smith"

The last case would be bonus points if there was a way to make it work.

My first idea was to just to modify the query to

SELECT * FROM table
WHERE table.firstName + ' ' +  table.lastName LIKE '%' + @searchString + '%'

But that was shot down because we don't want someone searching on just the letter 'a' for example to return tons of records, which the double wildcard would create.

Are there any tricks to doing this kind of string split/search? This can't be the first time this has been a problem but searching the internet I haven't been able to find anything except "split on space, but note that it won't work if there are 3 or more words in the name."

I feel like something along the lines of including the "between names" as part of both @firstName and @lastName and doing something clever, or making the SQL part more generic and then doing extra filtering with LINQ in my C# code, but a solution is eluding me.

Answer 1

I have done this by:

1. Using a wildcard to bookend each space separated name against both columns (I could not control name order or require first and last name).
2. I then assign a score based on each match (i.e. was it a whole word match and were there matches in both the first and last name columns)
3. I also had a minimum number of letters to search (min 2 letters)

This worked well for me. You can still generate a large number of matches for small words. The ranking of matches will push poor partial matches to the bottom of the list.