Fundamentals of Patterns Most or all of the criteria we have specified so far with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact value in records but you want to specify some type of approximation. To let you do this, the SQL provides the LIKE operator.
A Pattern that Exactly Matches a Value The most basic comparison performed on a string-based field is to find out whether it is equal to a certain string. We already know that this operation can be performed using the = operator. Here is an example: SELECT PropertyNumber,
City,
PropertyType,
PropertyNumber,
City,
PropertyType,
Condition,
Bedrooms,
Bathrooms,
FinishedBasement,
Stories,
MarketValue
FROM Properties
WHERE PropertyType = "single family";
The LIKE operator can be used to perform the same operation. To do this, use the following formula: WHERE column-name LIKE value Here is an example: SELECT PropertyNumber,
City,
PropertyType,
PropertyNumber,
City,
PropertyType,
Condition,
Bedrooms,
Bathrooms,
FinishedBasement,
Stories,
MarketValue
FROM Properties
WHERE PropertyType LIKE "single family";
This and the previous code produce the same result:
To make the operation easier to read, you can put it in parentheses. The above code can be written as follows: SELECT PropertyNumber,
City,
PropertyType,
PropertyNumber,
City,
PropertyType,
Condition,
Bedrooms,
Bathrooms,
FinishedBasement,
Stories,
MarketValue
FROM Properties
WHERE (PropertyType LIKE "single family");
Negating a Pattern As done with all Boolean operations, there are ways you can negate a LIKE operation. Probably the easiest way is to precede the expression with the NOT operator. Here is an example: SELECT StateName,
AreaSqrKms,
AdmissionUnionOrder,
Region
FROM States
WHERE Region NOT LIKE 'New England';
If you precede the expression with NOT, it is recommended that you put the section after NOT between parentheses. As an alternative, in most cases, you can also precede the LIKE keyword with NOT. Here is an example: SELECT StateName,
AreaSqrKms,
AdmissionUnionOrder,
Region
FROM States
WHERE NOT Region Like 'New England';
You can also negate a negation. Here is an example: SELECT StateName,
AreaSqrKms,
AdmissionUnionOrder,
Region
FROM States
WHERE NOT States.Region NOT Like 'New England';
If you negate a negation, you can get the result as though none of both negations was used.
Sorting Records If you are visually creating a query, you can sort the records using the Sort combo box in the bottom side of the window and for the field of your choice. Patterning by a Wildcard Matching a Character The idea of using a LIKE operator is to give an approximation of the value you want to compare to a field. To formulate this approximation, you use some specific symbols referred to as wildcards. They are combined with the LIKE operator. If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in n*, the condition would consist of finding any string that starts with that letter, in this case n. If you want to apply the search to multiple occurrences of a character, use as many combinations of the letter delimited by *. Remember that you can negate a LIKE condition by preceding it with NOT. Here is an example:
|
||||||
Practical
Learning: Introducing Patterns
to switch the query to the Design View
