[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

How to avoid wildcard characters in query


I am trying to avoid (or somehow convert to string) wildcard characters in my query for example (only end of query):

WHERE searchstring LIKE '% 123_ %'

is it possible to somehow sql treat 123_ as string and not to treat character _ as wildcard.

I am using:
sSql = sSql + " AND user LIKE '%" & Text1.Text & "%'"

now when in Text1.Text is found square bracket character [ it produces error, how can I solve this problem?

  • 4
  • 2
2 Solutions
Jim P.Commented:
This an ADP?

Then it would be
 WHERE searchstring LIKE '% 123\_ %' escape '\'
damirselAuthor Commented:
sorry don't understand your question "ADP?"

this is not working it pops up error message: 'syntax error missing operator...'
Jim P.Commented:
Is this an Access Data Project connected to a SQL server?

Then the wildcards are percent "%" and  underscore "_".  If this is linked tables then the wildcards are the asterick "*" and question mark "?" and you don't have to worry about the _.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

damirselAuthor Commented:
I am using VB with ADO (Microsoft.Jet.OLEDB.4.0) to connect to mdb.
Jim P.Commented:
>>I am using VB with ADO (Microsoft.Jet.OLEDB.4.0) to connect to mdb.

Then it is

sSql = sSql + " AND user LIKE '*" & Text1.Text & "*'"
if you want to use a wildcard to look for, as i want to look for  a _, then you have to place the wildcard in square brackets: so like "*123[_]*"

Good luck, Marc
Jim P.Commented:
From the VB Help:
Wildcard Characters Used in String Comparisons

Built-in pattern matching provides a versatile tool for making string comparisons. The following table shows the wildcard characters you can use with the Like operator and the number of digits or strings they match.

Character(s) in pattern Matches in expression
?       Any single character
*       Zero or more characters
#       Any single digit (09)
[charlist]       Any single character in charlist
[!charlist]       Any single character not in charlist

A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in expression and can include almost any characters in the ANSI character set, including digits. In fact, the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only if enclosed in brackets. The closing bracket ( ]) can't be used within a group to match itself, but it can be used outside a group as an individual character.


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now