English

How To: Use WHERE clauses

Summary

Instructions provided describes how to use WHERE clauses. Spatial Query and Data Manager Web services allow the filtering of results when using a WHERE clause.

Procedure

The following are examples of some of the ways to use WHERE clauses:

  • Use a basic WHERE clause.

    For example, City='Murrieta'.
    Result: locations in Murrieta.

  • Put more than one clause together.

    AND finds records that meet both criteria:
    For example, Type='Amusement Parks' AND State='CA'.
    Result: Amusement Parks in CA.

    OR finds records that meet one or both criteria:
    For example, Type='Amusement Parks' OR State='CA'.
    Result: Amusement Parks located anywhere and all places within CA.

  • Use different types of comparison operators. These can be used with letters and numbers.

    > is strictly 'greater than':
    For example, Name>'D'.
    Result: Names beginning D-Z and characters like ~ and -.

    <= is 'less than or equal to':
    For example, Zone<='60000'.
    Result: Zones 60000 and smaller.

    <> is 'not equal to':
    For example, State<>'CA'.
    Result: all states that are not CA.

  • Use extended comparisons.

    BETWEEN tests whether a value is between two other values:
    For example, City BETWEEN 'E' AND 'L'.
    Result: Cities such as Fedora that fall between E and L alphabetically.

    IN tests whether a value is in a list of possible values:
    For example, Type IN ('red', 'blue', 'green').
    Result: All Types are that either red, blue or green.

  • Use LIKE for pattern matching.

    _ tests for exactly one wildcard character:
    For example, State LIKE 'W_'.
    Result: States that begin with W, such as WA, WI, WV, and WY.

    % tests for zero to multiple wildcards:
    For example, City LIKE '%ing'.
    Result: Cities that end in 'ing' such as Redding.

  • Use NOT for negating a clause.

    For example, NOT Type='red'.
    Result: Types that are not red.

Related Information