Library code snippets
Access, VB and SQL Server wildcard characters
Visual Basic gives you several ways to provide pattern-matches in a
search string. Typically, you use pattern-matching characters for two
purposes: to search for strings in a VB application's GUI or variable,
or to look for items within a database, such as Access or SQL. When you
search for pattern matches in a database, however, depending on which
database you're using, different characters perform different matches.
The following list shows the different characters used by each database:
Required Match
Keep in mind that when you use Visual Basic's SQL Builder to create SQL
Any single character
Access/VB: ? SQLServer: _
Zero or more characters
Access/VB: * SQLServer: %
Any single digit (0-9)
Access/VB: # SQLServer: n/a
Any single character in charlist
Access/VB: [charlist] SQLServer: [charlist]
Any single character not in charlist
Access/VB: [!charlist] SQLServer: [^charlist]
statements, it only accepts SQL Server pattern-matching characters--even
when you're pulling data from an Access database.
James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
Update Sql Two Tables, Page Rank Update List
by StevenJ (0 replies)
-
Update Sql Multiple Tables, Windows Update Blank Page Windows Xp
by StevenJ (0 replies)
-
Update Sql Statement. Unable To Update Public Freebusy Data Outlook 2002
by StevenJ (0 replies)
-
Replicate Sql Express - Heuer Replica Tag Watch Womens
by JamesO (0 replies)
Related podcasts
-
Stack Overflow: Podcast #28
This is the twenty-eighth episode of the StackOverflow podcast, where Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. Jeff met the inestimable Joey DeVilla aka Accordion Guy...
customer ids beginning with "C10" (can have any number of characters or no characters at all)
17 found from select * from booking where [customer id num] like 'C10*'
customer ids beginning with "C100" and one character
16 found from select * from booking where [customer id num] like 'C100?'
customer ids beginning with "C100" and two characters
17 found from select * from booking where [customer id num] like 'C10??'
customer ids beginning with "C100" and one character of 1,2 or 3
10 found from select * from booking where [customer id num] like 'C100[123]'
customer ids beginning with "C100" and one character which is not one of 1,2,3
6 found from select * from booking where [customer id num] like 'C100[!123]'
customer ids beginning with "C100" and one number
16 found from select * from booking where [customer id num] like 'C100#'
customer ids beginning with "C100" and two numbers
17 found from select * from booking where [customer id num] like 'C10##'
This thread is for discussions of Access, VB and SQL Server wildcard characters.