|
|
|
|
|
This section provides a brief summary of the syntax of the Where clause. For fuller information, consult the SQL Reference manual for your database.
Where Clause Type |
Example |
Simple |
col1 = 4 |
Text compare |
col1 = 'Test' |
Not equal |
col1<> 'Test' |
Compare date |
col1 = '2007-12-07' |
Multiple values |
col1 in ('Test1','Test2','Test3') |
Starts with |
col1 like 'Test%' |
Contains |
col1 like '%Test%' |
Between two values |
col1 between 0 and 100 |
Text between |
col1 between 'JONES' and 'SMITH' |
Value is present in another table |
col1 in (select col2 from another_table) |
Value is not present in another table |
col1 not in (select col2 from another_table) |
Value is null |
col1 is null |
Value is not null |
col1 is not null |
The AQT Search Table window uses a simplified version of the where clause. One significant difference is that Table Search uses * as the wild-card, whereas normal SQL syntax uses %.
Some databases are case sensitive, some are not. For instance Sybase, SQL Server and MS Access are case insensitive, Oracle and DB2 are case sensitive. This also depends to some extent on how the database has been configured.
For databases that are case sensitive, you will may to code your Where clause such as:
where ucase(cust_name) like '%SMITH%'
Where clauses such as this are often a poor option as the database will not use an index (if there is an index on cust_name). However if you wish to do a case-insensitive compare you have little choice other than to use this.
This LIKE clause allows you to do a wild-card search of a column. With this:
% (percent) |
Match any number of characters |
_ (underscore) |
Match a single character |
Examples:
Where clause |
Will match |
Will not match |
SMITH% |
SMITH, SMITHERS |
JOHN SMITH |
%SMITH% |
SMITH, SMITHERS,JOHN SMITH |
SMYTH, SMOOTH |
SM_TH |
SMITH, SMYTH |
SMITHERS, JOHN SMITH |
%SMITH |
SMITH, JOHN SMITH |
SMITHERS, SMYTH |
%SM_TH% |
SMITH, SMITHERS, SMYTH, JOHN SMITH |
SMOOTH |
%SM%TH% |
SMITH, SMYTH, JOHN SMITH, SMOOTHIES |
|
Most databases are very fussy about how dates are specified. For more on this see Date and time columns.
For MS Access, date values must be encapsulated with #s instead of quotes. Example:
where datecol = #2007-12-09#
If you have multiple Where clauses, you connect these with AND and OR clauses.