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 ( [, . . . |
Last Modified: 02-OCT-00