Using Wildcards in Queries (1 Viewer)

Status
Not open for further replies.

ajetrumpet

Banned
Local time
Today, 15:57
Joined
Jun 22, 2007
Messages
5,640
This thread will hopefully give some in-depth understanding of wildcards and their uses when querying an Access database:


First of all, wildcard characters can be catagorized as follows:

1) 'String' Group
* (asterik)

2) 'Character' Group
? (question), # (pound), [ ] (bracket), ! (exclamation)

3) 'Range' Group
[ ] (bracket), - (hyphen), ! (exclamation)

The key word to use when querying with wildcards is LIKE. Conversely, it can also be used with the word NOT (e.g. NOT LIKE) to give results that do not match the criteria you specify. The placement of the keyword(s) comes before the criteria input value(s). Here are some short explanations of each wildcard already mentioned:

? (Question)
This is a single character reference. It can be used as a substitute for any character (letters, numbers, special characters). Here are a couple of examples
(Input Criteria = Output Result being queried):
Code:
1) I Love MS A[COLOR="Red"]?[/COLOR]cess = [I]I Love MS A[COLOR="Red"]c[/COLOR]cess[/I]
2) I Love MS A[COLOR="Red"]??[/COLOR]e[COLOR="Red"]??[/COLOR] = [I]I Love MS A[COLOR="Red"]cc[/COLOR]e[COLOR="Red"]ss[/COLOR][/I]
# (pound)
This is also a single reference substitute. However, it is limited to numeric values only. Numeric values include any number, even if it has been declared as a non-numeric data type (such as text, or date. Here are some examples:
Code:
1) 1/[COLOR="Red"]##[/COLOR]/2007 = [I]1/[COLOR="Red"]12[/COLOR]/2007[/I] or [I]1/[COLOR="Red"]20[/COLOR]/2007[/I] or [I]1/[COLOR="Red"]31[/COLOR]/2007[/I], etc...
2) 1/[COLOR="Red"]#[/COLOR]/2007 = [I]1/[COLOR="Red"]1[/COLOR]/2007[/I] or [I]1/[COLOR="Red"]9[/COLOR]/2007[/I], etc...

3) This is not [COLOR="Red"]#[/COLOR] me = [I]This is not [COLOR="Red"]4[/COLOR] me[/I]
[ ] (bracket)
This is very useful, especially when you need to extract specific data. It can be used to help retrieve specific characters within larger strings. You can use it to find one character, multiple characters, or even a range. The only bad thing about the bracket is the limitation. It can only be used to query one space at a time. The other thing to remember here is when you're using it to find multiple numeric values. If you have more than one numeric in the bracket, they must be delimited somehow (if they're not, they will be evaluated as one long numeric string). Commas, colons and semicolons all work fine for this purpose. Here are some examples of the bracketing method:
Code:
1) SQL is tou[COLOR="Red"][g][/COLOR]h to learn = [I]SQL is tou[COLOR="Red"]g[/COLOR]h to learn[/I]
2) SQL is t[COLOR="Red"][w, o, n, m][/COLOR]ugh to learn = [I]SQL is t[COLOR="Red"]o[/COLOR]ugh to learn[/I]
3) SQL is tough to l[COLOR="Red"][e, a, r][e, a, r][e, a, r][/COLOR]n = [I]SQL is tough to l[COLOR="Red"]ear[/COLOR]n[/I]
4) SQL is t[COLOR="Red"][oswln][/COLOR]ugh to learn = [I]SQL is t[COLOR="Red"]o[/COLOR]ugh to learn[/I]

5) This is the [COLOR="Red"][1, 3, 5, 9][/COLOR]th example = [I]This is the [COLOR="Red"]5[/COLOR]th example[/I]
- (hyphen)
This is very simple. It just specifies a range of values within a bracket. Examples:
Code:
1) This is si[COLOR="Red"][k-p][/COLOR]ple = [I]This is si[COLOR="Red"]mp[/COLOR]le[/I]
2) This is si[COLOR="Red"][g-p][g-p][/COLOR]le = [I]This is si[COLOR="Red"]mp[/COLOR]le[/I] [U]or[/U] [I]This is si[COLOR="Red"]ng[/COLOR]le[/I]

3) F[COLOR="Red"][a-p][/COLOR]ll = [I]F[COLOR="Red"]a[/COLOR]ll[/I] [U]or[/U] [I]F[COLOR="Red"]e[/COLOR]ll[/I] [U]or[/U] [I]F[COLOR="Red"]i[/COLOR]ll[/I]
! (exclamation)
Also called the "bang" symbol, this functions in a way similar to the not keyword. The only difference between the two is the context in which they are used. The bang operator is used with brackets:
Code:
1) [COLOR="Red"][!3-7][/COLOR]st example = [I][COLOR="Red"]1[/COLOR]st example[/I]

2) F[COLOR="Red"][!a-e][/COLOR]ll = [I]F[COLOR="Red"]i[/COLOR]ll[/I] [U]or[/U] [I]F[COLOR="Red"]u[/COLOR]ll[/I]
* (asterik)
This is probably the most confusing to work with. The part that is commonly misconstrued is the concatenation of the character with other strings (using the ampersand ("&")).
I use what I call the "connector" rule to determine weather or not my criteria will give me the result I want. Here is the basic principal:

If the * is connected to a string, it applies only to one non space-delimited string. If it is not connected to a string, it applies to the entire field value, relative to its placement within the criteria section as a whole. Here are some examples to illustrate:
Code:
1) Mic[COLOR="Red"]*[/COLOR]oft = [I]Mic[COLOR="Red"]ros[/COLOR]oft[/I]
2) Micro[COLOR="Red"]*[/COLOR] = [I]Micro[COLOR="Red"]soft[/COLOR][/I]

3) [COLOR="Red"]* &[/COLOR] ver = [I][COLOR="Red"]SQL Ser[/COLOR]ver[/I]
4) SQ [COLOR="Red"]& *[/COLOR] = [I]SQ[COLOR="Red"]L Server[/COLOR][/I]

5) [COLOR="Red"]* & [/COLOR]example = [I][COLOR="Red"]This is the last[/COLOR] example[/I]
6) [COLOR="Red"]* & [/COLOR]st exam [COLOR="Red"]& *[/COLOR] = [I][COLOR="Red"]This is the la[/COLOR]st exam[COLOR="Red"]ple[/COLOR][/I]
That's all folks. Just remember to include those quotation marks! :)
 
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom