Java Reference
In-Depth Information
Using the LIKE and NOT LIKE operators
The
LIKE
operator — and its negation, the
NOT LIKE
operator — combined with the wild card provide
a very powerful tool for string comparison. The wild cards are as follows:
Underscore ( _ ), the single character wild card
Percent ( % ), the multi-character wild card
For example, to find all records in the Contact_Info table with last name starting with "C," you would
write a query using
LIKE
as follows:
SELECT * FROM Contact_Info WHERE Last_Name LIKE 'C%';
Similarly, to find all records where the Last_Name contains the letter "o" in the second position, the
query would look like this:
SELECT * FROM Contact_Info WHERE Last_Name LIKE '_o%';
NOT LIKE
works in very much the same way as
LIKE
. For example, to find all records in the
Contact_Info table with last name
NOT
starting with the letter "C," you would write a query using
NOT
LIKE
as follows:
SELECT * FROM Contact_Info WHERE Last_Name NOT LIKE 'C%';
Using the concatenation operator
The concatenation operator is used to concatenate two strings. It is represented by the symbol, +, in
SQL, Access, and Sybase; Oracle accepts || as the concatenation operator. For example, to return the
last name followed by the first name separated by commas, you would use the following query:
SELECT Last_Name + ', ' + First_Name AS NAME FROM Contact_Info;
Caution
The concatenation operator is one of the SQL features that varies from one flavor of
SQL to another (as mentioned above). It is frequently worth checking the
documentation for the version of SQL you are using when you encounter problems.
Logical Operators
SQL provides several logical operators to combine two or more conditions in the
WHERE
clause of a
SQL statement. These logical operators are as follows:
AND
OR
NOT
Using the AND operator
The
AND
operator is used to combine two or more comparisons, all of which must evaluate to
TRUE
for
the comparison to be valid. If any of the expressions are false,
AND
returns
FALSE
. For example, to
find all records in the Contact_Info table with a Last_Name of Corleone who live in New York, you
would use this query:
SELECT * FROM Contact_Info WHERE Last_Name = 'Corleone' AND City = 'New York';
