Progress
SQL-89
Guide and Reference
Specifying Search Conditions
The WHERE clause of the SELECT statement specifies search conditions for the data you want to display. If you omit the WHERE clause, all rows are included in the retrieval set. The WHERE clause can also specify join conditions. For more information, see the "Selecting Data from Multiple Tables (Joins)" section.
This next example selects the name, address, city, and state columns from the customer table. The WHERE clause specifies customers from Massachusetts only.
A WHERE clause consists of one or more search conditions connected by the logical operators AND, OR, and NOT. All of the following are search conditions:
In the following example, the WHERE clause specifies customers whose customer number is greater than 10.
The BETWEEN search condition is equivalent to:
expression1 >= expression2 AND expression1 <= expression3
The following example uses BETWEEN and NOT BETWEEN to select items.
This search condition tests whether a column contains a null value. See "Progress/SQL-89 Components," for information on null values.
The following SELECT statement uses the IS NULL phrase to find which customers’ orders have not been shipped.
NOTE: This SELECT also joins the customer and order tables. For more information on joins, see the "Selecting Data from Multiple Tables (Joins)" section.
The LIKE search condition compares character values in a column to a character string that is enclosed in either single or double quotation marks. The string can be a pattern created with any number of characters, including the percent sign (%) and underscore ( _ ) wildcard characters. The percent sign matches zero or more characters. The underscore matches any single character.
The following example selects all USA customers with postal-codes beginning with 02.
To search for the percent sign or underscore, you must declare an escape character to disable the use of the character as a wildcard. To declare an escape character, use the keyword ESCAPE followed by the escape character enclosed in either single or double quotation marks. In the search string, precede the percent sign or underscore with the declared escape character.
In the following example, the vertical bar ( | ) is used as the escape character to search for the percent sign in the last position of terms.
To use a backslash (\) as the escape character, you must specify it as “\\”, since “\” is already defined as an escape character in Progress.
This search condition tests whether the expression is equal or not equal to any item in the value-list or any value that the SELECT statement returns. If you enter a list of values, separate them with commas.
The following query uses the IN syntax to select customers from New England.
The CONTAINS search condition tests whether any portion of any item that the query returns contains the string. The test ignores case.
The following query uses the CONTAINS search condition to select customers who are on “credit hold.”
The MATCHES search condition tests whether any item that the query returns matches the string. The test ignores case.
Unlike the CONTAINS search condition (and = operator), the MATCHES search condition recognizes the wildcard (*) (zero or more of any character) and “.” (one of any character).
The following query uses the MATCHES search condition to select customers whose “state” column begins with “m” and ends with “x,” such as “Middlesex.”
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |