How To: Use WHERE clauses
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.
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.
Article ID: 000005703
Software: ArcWeb Services 1.0, 2.0, 2006, 2006.1, 4.0