Access 2010 SQL查询仅在完整单词的字符串中查找部分匹配 [英] Access 2010 SQL Query find partial match in string of full word only

问题描述

我希望这是一个简单的方法，只是找不到如何获得想要的结果，也许我在SQL中使用了错误的关键字?

I hope this is a simple one, I just can't find how to get the result I want, maybe I am using the wrong keyword in SQL?

我正在搜索一个包含全名字段的Employee表，该字段可以是"Sam"或"Evans先生"或"Sam Evans先生"

I am searching an Employee table which contains a Full Name field, this field can be "Sam" or "Mr Evans" or "Mr Sam Evans"

我正在尝试与另一个包含名称字段的名称表进行部分匹配-这通常是一个简短的名称，例如"Sam"或"Evans"，但可能是"Sam Evans"

I am trying to find partial matches with another Names table which contains a Name field - this is usually a short name such as "Sam" or "Evans" but could be "Sam Evans"

我尝试过，有和没有*的情况如下

I have tried like, with and without * as follows

SELECT tblEmployee.FullName, tblNames.Name
FROM tblEmployee, tblNames
WHERE tblEmployee.FullName Like "*" & tblNames.Name & "*"

以及我想要的结果，此查询可以返回我不希望的"Samson先生"的雇员全名.我只想看到在字符串中一个单词的一部分中包含完整单词"Sam"而不是FullName的结果.所以我想看Sam Evans先生，Sam Smith先生和Sally Evans女士，等等.

as well as the results I do want, this query can return Employee FullName of "Mr. Samson" which I don't want. I only want to see results contaning the full word "Sam" not FullNames with Sam in part of a word within the string. So I want to see Mr. Sam Evans and Mr. Sam Smith and Ms. Sally Evans, etc.

我希望这是有道理的.非常感谢您的帮助

I hope this makes sense. Many thanks for your help

我已经解决了这个问题-只是发布以防万一，它可能会帮助遇到类似问题的任何人.

I have solved this - just posting in case it might help anyone with a similar problem.

WHERE " " & tblEmployee.FullName & " " Like "* " & tblNames.Name & " *"

因此，基本上，通过在员工全名的开头和结尾添加一个空格，我捕获了所有正确的记录.我还没有意识到你可以像这样填补一个空缺，但是一位同事向我提出了建议！

so basically by adding a space at the beginning and the end of the Employee Full Name, I catch all the correct records. I hadn't realised you could pad out a field like this but a colleague tipped me off!

推荐答案

SQL和访问中的通配符是%符号；

Wildcards in SQL and access are a % symbol;

如此

"SELECT tblEmployee.FullName, tblNames.Name FROM tblEmployee, tblNames
WHERE tblEmployee.FullName Like '%" & tblNames.Name & "%'"

将匹配字符串tblNames.Name

您只能在开头或结尾使用%来分别获得仅字符串开头/字符串结尾的匹配项.

You can use the % at the start or end only to get the match of just the start of the string / end of the string repectively.

编辑

edit

抱歉，您可以 在访问中使用*和?，但是您是否将查询作为字符串发送给某些VBA代码?还是尝试直接运行它?与直接在查询中一样，它无法将tblNames.Name字符串解析为查询，因此您需要从表单或其他代码形式中传递该字符串.

Apologies, you can use * and ? in access as you expect, however are you sending the query as a string to some VBA code? Or trying to run it directly? As directly in a query it'll not be able to parse the tblNames.Name string into the query and you'll need to pass that in from a form or other peice of code.

根据评论进行编辑

Edit based on comment

要选择一个特定的单词，只需要做一些工作即可:

To select a specific word it's just a bit of a work around required:

SELECT * FROM table WHERE field LIKE '* myWord *' OR field LIKE '* myWord.*'

您可以有选择地捕获情感开始，例如"MyWord *" 以及带有逗号，句号，感叹号等的单词...

You can optionally caputure start of sentances `LIKE 'MyWord *' and word with commas, fullstops, exclamtion marks etc...

您可以执行IN语句，并在查找表中保留所有变体，以使其易于维护.

You could do an IN statement and have all the variations in a lookup table to keep it easy to maintain as another option.

这篇关于Access 2010 SQL查询仅在完整单词的字符串中查找部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！