community.borland.com

Article #25212: Performing different types of searches in a WHERE clause

Problem:
The WHERE clause in an SQL statement can be used to perform searches and restrict data to 
different types of matches.  Several different types of searches are covered below.

Solution:
The types of matches and examples of these matches are covered below:

1. Exact Matches - use the aritmetic operators of = < > <= >= !< !> <> and != to search for specific 
records.  Some examples of this are:

  SELECT * FROM Employee WHERE emp_no  = 144;
  SELECT * FROM Employee WHERE dept_no > 200;


2. Range Matches - finds records that meet the search criteria where column(s) of values fall 
     within a specified range.  Some examples of this are:

  SELECT * FROM Employee WHERE 2 <= emp_no AND emp_no <= 100;
  SELECT * FROM Employee WHERE Last_Name BETWEEN "Jones" and "Smith";


3. Like Matches - search a string for a specific substring, if the substring is found then the record 
    meets the search criteria.  There are a couple of wildcard characters that make searching for 
    substrings easier.  The "_" (underscore) wildcard is a placeholder for a single character which 
    can be any character in the ASCII chart, the "%" is like the "_" wildcard except that it can represent
    multiple characters on the ASCII chart.  Some examples of like matches are:

  // Searches for names like Lee, Leung, and so forth . . .
  SELECT * FROM Employee WHERE Last_Name LIKE "Le_";

  // Search for last names starting with "Le"
  SELECT * FROM Employee WHERE Last_Name LIKE "Le%";

  // Search for last names ending with "er"
  SELECT * FROM Employee WHERE Last_Name LIKE "%er";

  // Case-sensitive Search for last names containing the substring "or"
  SELECT * FROM Employee WHERE Last_Name LIKE "%or%";


4. Starts With - searches a string for the specified starting characters.  Some examples of this are:

  SELECT * FROM Employee WHERE Last_Name STARTING WITH "Le"

  SELECT * FROM Employee WHERE Last_Name NOT STARTING WITH "Le"


5. Case-Insensitive Contains - performs a case-insensitive search for substrings.  
    Some examples of this are:

  // Both SELECT statements will bring back the same identical data set
  // since the search is case-insensitive
  SELECT * FROM Employee WHERE Last_Name CONTAINING "or"
  
  SELECT * FROM Employee WHERE Last_Name CONTAINING "OR"


7. Match Found In - performs an evaluation based on the syntax of:

   IN ( [,  . . . |