SQL LIKE operator with parameters and wildcards

问题:

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:

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

回答1:

MS Access uses * as a wildcard not %, so your query will be trying to match literal '%' characters. Use * instead unless you are using ADO.

http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx

回答2:

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

回答3:

You are not using from in your statement

PARAMETERS Client Text ( 255 );
SELECT * from table

回答4:

How about using REGEXP function in 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:

SELECT *
WHERE tbl_IncomingChecks.Client REGEXP @Client
ORDER BY tbl_IncomingChecks.Client;

• SQLFIDDLE DEMO.

---

As per OP's update on RDBMS as MS ACCESS

If you have a more sophisticated pattern you could use Regexp object within a MS Access UDF. However in current scenario you are better off with 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;