I'm trying to perform a full-text search on a table which contains email addresses.
我正在尝试在包含电子邮件地址的表上执行全文搜索。
Let's say my table contains the email address: a.b.builder@realestate.com
假设我的表包含电子邮件地址:a.b.builder@realestate.com
Now, because of word breakers the periods in this email address function as a separator. The default SQL Server Stoplist prevents single characters to be indexed (for obvious reasons).
现在,由于断字符,此电子邮件地址中的句点用作分隔符。默认的SQL Server停止列表可以防止对单个字符建立索引(出于显而易见的原因)。
Normally not an issue and searching for the address works just fine. However, I want to be able to search for parts of the address.
通常不是问题,搜索地址工作得很好。但是,我希望能够搜索地址的一部分。
I'd like to search for "a.b.builder@real" by using the query below. This will not work unfortunately, because the address isn't indexed as "a.b.builder@real...".
我想使用下面的查询搜索“a.b.builder@real”。遗憾的是,这不起作用,因为地址没有被索引为“a.b.builder@real ...”。
SELECT * FROM [Addressbook] a WHERE CONTAINS([a].*, ' "a.b.builder@real*" ')
SELECT * FROM [Addressbook] a WHERE CONTAINS([a]。*,'“a.b.builder@real*”')
Any suggestions on how to solve this? Test example on SQL Fiddle.
有关如何解决这个问题的任何建议?关于SQL Fiddle的测试示例。
1 个解决方案
#1
1
You can use LIKE operator:
您可以使用LIKE运算符:
SELECT * FROM [Addressbook] a WHERE EmailAddress LIKE 'a.b.builder@real%';
You can only pass a string value to a second parameter in CONTAINS. I don't think there is a way of bypassing this without compromising performance in the first place.
您只能将字符串值传递给CONTAINS中的第二个参数。我认为没有办法在不影响性能的情况下绕过这一点。
So if you pass a wildcard it will take the whole string value as condition:
因此,如果您传递通配符,它将把整个字符串值作为条件:
SELECT * FROM [Addressbook] a WHERE CONTAINS([a].*, 'a.b.builder@real*')
You can see a link hire for functions that you can use on strings link. The only way i can see that you can solve this is by writing some sort of LIKE function yourself then passing the value that is return'd in to a CONTAINS. But that will make your performance even worse than just using a LIKE operator.
您可以看到可以在字符串链接上使用的函数的链接租用。我可以看到你可以解决这个问题的唯一方法是自己编写一些LIKE函数,然后将返回的值传递给CONTAINS。但这会使您的性能甚至比仅使用LIKE运算符更糟糕。
#1
1
You can use LIKE operator:
您可以使用LIKE运算符:
SELECT * FROM [Addressbook] a WHERE EmailAddress LIKE 'a.b.builder@real%';
You can only pass a string value to a second parameter in CONTAINS. I don't think there is a way of bypassing this without compromising performance in the first place.
您只能将字符串值传递给CONTAINS中的第二个参数。我认为没有办法在不影响性能的情况下绕过这一点。
So if you pass a wildcard it will take the whole string value as condition:
因此,如果您传递通配符,它将把整个字符串值作为条件:
SELECT * FROM [Addressbook] a WHERE CONTAINS([a].*, 'a.b.builder@real*')
You can see a link hire for functions that you can use on strings link. The only way i can see that you can solve this is by writing some sort of LIKE function yourself then passing the value that is return'd in to a CONTAINS. But that will make your performance even worse than just using a LIKE operator.
您可以看到可以在字符串链接上使用的函数的链接租用。我可以看到你可以解决这个问题的唯一方法是自己编写一些LIKE函数,然后将返回的值传递给CONTAINS。但这会使您的性能甚至比仅使用LIKE运算符更糟糕。