473,287 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,287 developers and data experts.

WHERE Clause using Wildcards & Pattern Matching

NeoPa
32,554 Expert Mod 16PB
ANSI-89 v ANSI-92
Before we get into all the various types of pattern matching that can be used, there are two ANSI standards used for the main types of wildcard matching (matching zero or more characters or simply matching a single character) :
ANSI-89 - Mainly used only by Jet / ACE SQL
ANSI-92 - Mainly used by SQL Server and other grown-up products
In the later versions of Access it is now possible to select ANSI-92 compatibility as an option, instead of the ANSI-89 compatibility.

Using Wildcard Characters 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.

Pattern Match Character(s) in expressions :
Any single character - '?' [ANSI-89] or '_' (underscore) [ANSI-92]
Zero or more characters - '*' [ANSI-89] or '%' [ANSI-92]
Any single digit (0 - 9) - '#'
Any single character found within charlist - [charlist]
Any single character not found within charlist - [!charlist]

You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in an expression, and charlist can include almost any characters in the ANSI character set, including digits. You can use the special characters opening bracket ([ ), question mark (?), underscore (_), asterisk (*), percent (%) and number sign (#) to match themselves directly only if enclosed in brackets. You cannot use the closing bracket (]) within a group to match itself, but you can use it outside a group as an individual character.

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] in a pattern results in a match if the corresponding character position in the expression contains any of the uppercase letters in the range A through Z. You can include multiple ranges within the brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.

It is important to note that the ANSI-92 SQL wildcards (%) and (_) are only available with Microsoft® Jet version 4.X and the Microsoft OLE DB Provider for Jet. They will be treated as literals if used through Microsoft Access or DAO.

Other important rules for pattern matching include the following :

An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist are found in the expression. When used outside brackets, the exclamation mark matches itself.
You can use the hyphen (-) either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself. In any other location, the hyphen identifies a range of ANSI characters.
When you specify a range of characters, the characters must appear in ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] is not.
The character sequence [] is ignored; it is considered to be a zero-length string ("").
Jul 13 '07 #1
0 10520

Sign in to post your reply or Sign up for a free account.

Similar topics

100
by: Roose | last post by:
Just to make a tangential point here, in case anyone new to C doesn't understand what all these flame wars are about. Shorthand title: "My boss would fire me if I wrote 100% ANSI C code" We...
4
by: Luke Wu | last post by:
I am just wondering what the following terms usually mean: 1) "Standard C" 2) "K&R C" 3) "ANSI C" I am pretty sure "ANSI C" usually refers to the C89 standard, but what
13
by: Roy Hills | last post by:
I've seen two different function prototype formats used for ANSI C, and I'm unsure as to which is the correct or preferred one. 1st Format (this is what I use) type function(type, type, type);...
6
by: vpuvvada | last post by:
Please help me out in finding ANSI Standard specification documents for C and C++ Thanks Venkat
5
by: alsor.zhou | last post by:
hi all, I'm searching the `ANSI C89 specification' and the related. Since I tried with `ansi c89 standard' and `ansi c89 specificatioin' in google, there was none useful links return. anybody...
83
by: sunny | last post by:
Hi All What is C99 Standard is all about. is it portable, i mean i saw -std=C99 option in GCC but there is no such thing in VC++.? which one is better ANSI C / C99? can i know the major...
9
by: emagzz | last post by:
Hi all, I need to convert many text file from ANSI to UNICODE. Some body knows if there is a free utility that can do this from the command line so as I can use it inside a batch file. ...
0
by: MaartenVR | last post by:
sub: MS SQL server and (missing) ANSI DATE-datatype I’m working at a company who has developed a large client/server application in Delphi 6, with Interbase as the DB-server (both Borland...
127
by: bz800k | last post by:
Hi Does this code satisfy ANSI C syntax ? void function(void) { int a = 2; a = ({int c; c = a + 2;}); /* <<-- here !! */ printf("a=%d\n", a);
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.