Query Operators

Like

Use like to search for values using either a wildcard or bracketed [] list of values.  If you don't use either of these, then the like keyword works just like =

Example:  LIKE A%= Find all items beginning with A

NOT Like

Use not like to search for values not matching using either a wildcard or bracketed [] list of values. If you use neither wildcard nor the brackets, then not like works just like <>

Example:  NOT LIKE A% = Find all items that do not begin with an A

%  Wild card

Designates a wild card. It requires the LIKE keyword.   A wild card character could also be used as a prefix or suffix in conjunction with the Like operator and a known character.

Example: Like A%C will return ABC as well as ABBC and AC

_ (underscore) wildcard

The underscore character is also a wild card.  However, unlike the % wildcard, it will only match to 0 or 1 characters.  It too requires the Like keyword.

Example:  Like A_C will return ABC, but would not return ABBC

=   Equal to

This function includes any values equal to the search criteria.  If no operator is entered, the = is assumed.

If = is entered and no value is entered along with it, then the system will look for missing values (NULL values or an empty string if the field is a string).

Examples:  

= 1000 searches for the value of 1000.  Just entering 1000 works the same

= searches for missing entries (null)

>  Greater than

This function includes any item in the search criteria greater than the stated values. This operator can be used in conjunction with the AND operator to accomplish a between operator for both number and date fields.

Example:  

> 100    searches for anything greater than 100

>          searches for anything that has any value entered (not empty or null)

<   Less than

This function includes any item in the search criteria less than the stated values. This operator can be used in conjunction with the AND operator to accomplish a between operator for both number and date fields.

For example:  < 100

< >

Not equal To

This function includes all items in the search criteria other than the stated value.

For example:  <> 100  (Any value other than 100)

> =  Greater than, or equal to

This function includes all items greater than or equal to the stated search criteria.

For example:  >= 100  (any value 100 or greater)

< =   Less than, or equal to

This function includes all items less than or equal to the stated search criteria.

For example:  <= 100  (any value 100 or less)

AND

Setting parameters on subsequent rows with the prefix “and” designates an AND operator. Both conditions have to be true for an item to populate the window. A BETWEEN function is accomplished by using > or < in conjunction with the AND operator.

OR

Setting parameters on subsequent rows without a prefix designates an OR operator. Either parameter could be true for an item to populate the window.

IN

List of values.  Works like OR but within same cell.

Example:

IN ('ABC', 'DEF') is the same as (value = ABC OR value = DEF)

IN (1001,1002)

note that numbers do not need quotes, but string/characters do

[] Brackets

The use of brackets allows the user to specify that one of the values within the brackets must match.  It must be used with the Like Keyword.

Example:  Like A[BC]C

Returns ABC and ACC but not AAC

Example:  like [6,7]%

Returns everything starting with 6 or 7.  

Example:  like [6-9]%

Returns everything starting with 6 through 9.  Starts with 6,7,8 or 9 and all other parts are a wildcard.  

Example:  not like [6,7]%

Returns everything that does not start with either 6 or 7.  

Note that this will not work with the Oracle database.