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.

SELECT Name, Address, City, State
  FROM Customer
  WHERE State = ’MA’. 

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:

SYNTAX
expression relational-operator expression 

In the following example, the WHERE clause specifies customers whose customer number is greater than 10.

SELECT Cust-Num, Name
  FROM Customer
    WHERE Cust-Num > 10. 

SYNTAX
expression1 [ NOT ] BETWEEN expression2 AND expression3 

The BETWEEN search condition is equivalent to:

expression1 >= expression2 AND expression1 <= expression3

The following example uses BETWEEN and NOT BETWEEN to select items.

SELECT Item-Num, Item-Name
  FROM Item
    WHERE Item-Num BETWEEN 10 AND 20
       AND Item-Num NOT BETWEEN 15 AND 17. 

SYNTAX
column-name IS [ NOT ] NULL 

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.

SELECT Name, City, State, Order-Date
  FROM Customer, Order
    WHERE Ship-Date IS NULL. 

NOTE: This SELECT also joins the customer and order tables. For more information on joins, see the "Selecting Data from Multiple Tables (Joins)" section.

SYNTAX
column-name [ NOT ] LIKE "string" [ ESCAPE "character" ] 

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.

SELECT Cust-Num, Name
  FROM Customer
     WHERE Country = "USA" AND Postal-Code LIKE "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.

SELECT Name, Terms
  FROM Customer
    WHERE Terms LIKE "|%" ESCAPE "|". 

To use a backslash (\) as the escape character, you must specify it as “\\”, since “\” is already defined as an escape character in Progress.

SYNTAX
expression [ NOT ] IN ({ value-list | SELECT-statement } ) 

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.

SELECT Name, Address, City, State
   FROM Customer
      WHERE State IN ("MA", "NH", "VT", "ME", "CT", "RI"). 

SYNTAX
expression CONTAINS "string" 

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.”

SELECT * FROM Customer
      WHERE Comment CONTAINS ’credit hold’. 

SYNTAX
expression MATCHES "string" 

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.”

SELECT * FROM Customer
      WHERE State MATCHES ’m*x’. 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095