WHERE clauses with columns containing text data, SQL supports a number of useful operators
to do things like case-insensitive string comparison and wildcard pattern matching. We show a few common
text-data specific operators below:
|=||Case sensitive exact string comparison (notice the single equals)||col_name = "abc"|
|!= or <>||Case sensitive exact string inequality comparison||col_name != "abcd"|
|LIKE||Case insensitive exact string comparison||col_name LIKE "ABC"|
|NOT LIKE||Case insensitive exact string inequality comparison||col_name NOT LIKE "ABCD"|
|%||Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)||col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS")
|_||Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)||col_name LIKE "AN_"
(matches "AND", but not "AN")
|IN (…)||String exists in a list||col_name IN ("A", "B", "C")|
|NOT IN (…)||String does not exist in a list||col_name NOT IN ("D", "E", "F")|
All strings must be quoted so that the query parser can distinguish words in the string from SQL keywords.
We should note that while most database implementations are quite efficient when using these operators, full-text search is best left to dedicated libraries like Apache Lucene or Sphinx. These libraries are designed specifically to do full text search, and as a result are more efficient and can support a wider variety of search features including internationalization and advanced queries.
Here's the definition of a query with a
WHERE clause again, go ahead and try and write some queries
with the operators above to limit the results to the information we need in the tasks below.
SELECT column, another_column, … FROM mytable WHERE condition AND/OR another_condition AND/OR …;