带参数和通配符的 SQL LIKE 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14105280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL LIKE operator with parameters and wildcards
提问by Chris568524
I have a query where I want to return all Clients that have a certain string in the name with wildcards on either side. So the input could be "Smith" and i want to return all things like "The John Smith Company" or "Smith and Bros". I want [Client] to be prompted so I set up the SQL like this:
我有一个查询，我想返回名称中具有特定字符串的所有客户端，两边都带有通配符。所以输入可能是“史密斯”，我想返回所有的东西，比如“约翰史密斯公司”或“史密斯兄弟”。我希望提示 [Client]，所以我设置了这样的 SQL：
PARAMETERS Client Text ( 255 );
SELECT *
WHERE (((tbl_IncomingChecks.Client) Like'%' + [Client] + '%')
ORDER BY tbl_IncomingChecks.Client;
The query is not returning any results. Please help
查询未返回任何结果。请帮忙
采纳答案by Lord Peter
MS Access uses * as a wildcard not %, so your query will be trying to match literal '%' characters. Use * instead unless you are using ADO.
MS Access 使用 * 作为通配符而不是 %，因此您的查询将尝试匹配文字 '%' 字符。除非您使用 ADO，否则请改用 *。
http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx
http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx
回答by Joshua
I feel like your problem is on the '+' operators shouldn't it read
我觉得你的问题出在“+”运算符上，不应该读
WHERE ((tbl_IncomingChecks.Client) Like Concat('%',[Client],'%'))
This got me in DB2
这让我进入了 DB2
回答by syed mohsin
You are not using from in your statement
您没有在语句中使用 from
PARAMETERS Client Text ( 255 );
SELECT * from table
回答by bonCodigo
How about using REGEXPfunction in MYSQL?
REGEXP在 MYSQL 中如何使用函数？
SELECT *
WHERE tbl_IncomingChecks.Client REGEXP concat('%', @Client, '%')
ORDER BY tbl_IncomingChecks.Client;
Or just simply use @client as the REGEXPto find all clients that contains this client name:
或者只是简单地使用 @client 作为REGEXP查找包含此客户端名称的所有客户端：
SELECT *
WHERE tbl_IncomingChecks.Client REGEXP @Client
ORDER BY tbl_IncomingChecks.Client;
As per OP's update on RDBMS as MS ACCESS
根据 OP 在 RDBMS 上更新为 MS ACCESS
If you have a more sophisticated pattern you could use Regexpobject within a MS Access UDF. However in current scenario you are better off with LIKE Concat('*',@client,'*')
如果您有更复杂的模式，您可以Regexp在 MS Access UDF 中使用对象。但是，在当前情况下，您最好使用LIKE Concat('*',@client,'*')
'-- you may even send the pattern as a parameter
'-- you may also send all the clients into the UDF itself for matching
'-- returning a set of matched names string
Function regexpFunc(ByRef strInput As String, ByRef clientName as String) As Boolean
Dim myRegex As New RegExp
Dim matchSet As MatchCollection
With myRegex
.MultiLine = False
.Global = True
.IgnoreCase = False
End With
myRegex.Pattern = clientName
If myRegex.Test(strInput) Then
'matching values can be collected here
'-- Set matchSet = myRegex.Execute(strInput)
RegexFunc = True
Else
RegexFunc = False
End If
End Function
Here is how you may use the above function in the query:
以下是在查询中使用上述函数的方法：
SELECT *
FROM MYTABLE
WHERE RegexpFunc(tbl_IncomingChecks.Client, "Smith")
ORDER BY tbl_IncomingChecks.Client;
