Using comparison operators in the WHERE clause

You can use comparison operators in the WHERE clause. The operators follow the syntax:

WHERE expression comparison-operator expression

See Comparison operators, and Expressions.

Notes on comparisons

  • 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 by going to the Extended Information tab of the Database Properties window.

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

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

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

The NOT operator negates an expression. Either of the following two queries 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;
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1