Java Reference
In-Depth Information
customers named "Michael," or whose names come after "Michael" in the alphabet,
you can use this query:
SELECT *
FROM CUSTOMERS
WHERE first_name >= 'Michael';
This query returns a result set like the one shown in
Table 3-5
.
Table 3-5:
Results of a Lexical String Comparison
ID
FIRST_NAME
MI
LAST_NAME
STREET
CITY
STATE
ZIP
100
Michael
A
Corleone
123 Pine
New
York
NY
10006
102
Sonny
A
Corleone
123
Walnut
Newark
NJ
12346
104
Vito
G
Corleone
23 Oak St
Newark
NJ
12345
105
Tom
B
Hagen
37
Chestnut
Newark
NJ
12345
Using the IS NULL operator
SQL's special NULL value represents an absence of data, so it can't be evaluated
using the comparison operators. SQL provides special IS NULL and IS NOT NULL
operators to test for NULL. If, for example, you add a column to the Customers Table
for FAX numbers, leaving it NULL when a contact doesn't have a fax number, you can
query the table for contacts with faxes as follows:
SELECT * FROM Customers WHERE FAX IS NOT NULL;
Using the LIKE and NOT LIKE operators
In addition to the comparison operators, SQL adds these dedicated operators for
testing for a substring within CHAR and VARCHAR variables:
LIKE
NOT LIKE
The LIKE operator, and its negation, the NOT LIKE operator, combined with
wildcards provide a very powerful tool for String comparison. The wildcards are as
follows:
Underscore ( _ ), the single character wild card
Percent ( % ), the multicharacter wild card
