使用包含 2 个以上单词的单个搜索字符串对名字和姓氏列执行搜索 [英] Performing a search on first and last name columns with a single search string that has more than 2 words

问题描述

我有一个查询，它当前采用单个用户提供的搜索字符串，并尝试搜索一个包含 firstName 和 lastName 单独列的表.如果搜索字符串中有空格，它运行的查询本质上是这样的:

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

等等.现在我们在第一个空格上拆分，所以第一个示例Bob van 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 + '%'

但这被否决了，因为我们不希望有人只搜索字母a"，例如返回大量记录，而双通配符会创建这些记录.

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.

进行这种字符串拆分/搜索有什么技巧吗?这可能不是第一次出现问题，但在互联网上搜索我找不到任何东西，除了空间分割，但请注意，如果名称中有 3 个或更多单词，它将不起作用."

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

我感觉像是将名称之间"作为@firstName 和@lastName 的一部分并做一些聪明的事情，或者使 SQL 部分更通用，然后在我的 C# 代码中使用 LINQ 进行额外过滤，但我找不到解决方案.

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.

推荐答案

我已经完成了:

1. 使用通配符将每个空格分隔的名称与两列相结合(我无法控制名称顺序或需要名字和姓氏).
2. 然后，我会根据每个匹配项分配一个分数(即是否为全字匹配以及名字和姓氏列中是否都有匹配项)
3. 我还需要搜索最少数量的字母(最少 2 个字母)

这对我来说效果很好.您仍然可以为小词生成大量匹配项.匹配排名会将差的部分匹配推到列表底部.

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.

这篇关于使用包含 2 个以上单词的单个搜索字符串对名字和姓氏列执行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！