You can use comparison operators in the WHERE clause. The operators follow the syntax:
WHERE expression comparison-operator expression
For more information about comparison operators, see Comparison operators. For a description of what an expression can consist of, see Expressions.
Sort orders In comparing character data, < means earlier in the sort order and > means later in the sort order. The sort order is determined by the collation chosen when the database is created. You can find out the collation by running the dbinfo utility against the database:
dbinfo -c "uid=DBA;pwd=sql"
You can also find the collation from Sybase Central. It is on the Extended Information tab of the database property sheet.
Trailing blanks When you create a database, you indicate whether trailing blanks are to be ignored or not for the purposes of comparison.
By default, databases are created with trailing blanks not ignored. For example, 'Dirk' is not the same as 'Dirk '. You can create databases with blank padding, so that trailing blanks are ignored. Trailing blanks are ignored by default in Adaptive Server Enterprise databases.
Comparing dates In comparing dates, < means earlier and > means later.
Case sensitivity When you create a database, you indicate whether string comparisons are case sensitive or not.
By default, databases are created case insensitive. For example, 'Dirk' is the same as 'DIRK'. You can create databases to be case sensitive, which is the default behavior for Adaptive Server Enterprise databases.
Here are some SELECT statements using comparison operators:
SELECT * FROM Products WHERE Quantity < 20; SELECT E.Surname, E.GivenName FROM Employees E WHERE Surname > 'McBadden'; SELECT ID, Phone FROM Contacts WHERE State != 'CA';
The NOT operator negates an expression. Either of the following two queries will find all Tee shirts and baseball caps that cost $10 or less. However, note the difference in position between the negative logical operator (NOT) and the negative comparison operator (!>).
SELECT ID, Name, Quantity FROM Products WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap') AND NOT UnitPrice > 10; SELECT ID, Name, Quantity FROM Products WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap') AND UnitPrice !> 10;