|
Like & Between Operators |
  
|
|
Like & Between Operators |
|
Like & Between Operators
The most significant thing to remember when using either of these two operators is that the construct of the comparison criteria uses the rules as outlined for Microsoft SQL and NOT those used by Microsoft Access. While the construction of the values to be compared are very similar, different wildcard characters are used.
The following information describing the LIKE and BETWEEN operators has been extracted from Microsoft SQL Books Online (modified to reflect DataPorter as required) and does not include all the information contained in that source. For more detailed information, please refer to the Microsoft SQL Books Online directly. These two operators are included for those who already have some familiarity with these and as such technical support is not provided in "how to use" either operator.
Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string.
Wildcard character |
Description |
Example |
% |
Any string of zero or more characters. |
WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title. |
_ (underscore) |
Any single character. |
WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on). |
[ ] |
Any single character within the specified range ([a-f]) or set ([abcdef]). |
WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. |
[^] |
Any single character not within the specified range ([^a-f]) or set ([^abcdef]). |
WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l. |
Table 8-1 LIKE wildcard characters
When you perform string comparisons with LIKE, all characters in the pattern string are significant, including leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.
Specifies a range to test.
Syntax
comparison_field [ NOT ] BETWEEN begin_expression AND end_expression
the complete syntax following the word BETWEEN must be entered into the comparison value box.
Arguments
comparison_field is the field to test for in the range defined by begin_expression and end_expression. comparison_field must be the same data type as both begin_expression and end_expression.
No data validation on the begin and end expressions is performed. If invalid values are entered, unpredictable results will occur.
NOT
Specifies that the result of the predicate be negated.
begin_expression / end expression
is any literal value that conforms to the data type of the comparison field
AND
Acts as a placeholder indicating that the comparison_field should be within the range indicated by begin_expression and end_expression.
Example
A. Using BETWEEN
This example returns title identifiers for books with year-to-date unit sales from 4,095 through 12,000.
comparison_field BETWEEN 4095 AND 12000
will result in all records being selected where there comparison value contains a value from 4095 to 12000 inclusive. This differs from a similar comparison using the > (greater than) and < (less than) operators such as comparison_field > 4095 AND comparison_field < 12000
which not include records containing either the value 4095 or 12000
B. Using NOT BETWEEN
comparison_field NOT BETWEEN 4095 AND 12000
Like example A - this will result in all records being selected where the comparison field does not contain a value from 4095 to 12000 inclusive.