Patterned Operators in Queries

Introduction to Logical Operators

So far, when addressing a condition, we assumed that it was true. The SQL provides an operator that, considering a given condition, would be negated. In other words, this operator finds the contrary of the stated condition. This operator is called NOT. An example would be

NOT (Gender = "Female")

Consider the query we saw earlier that allowed us to get a list of employees names and their departments. In fact, we wanted to include in our list only the employees who work at the corporate office:

Imagine that, instead of getting the list of employees who work at the corporate office, you are interested in the employees who don't work at the corporate office. To get this list, you can negate the "Corporate" condition that was stated earlier. This is done by preceding the condition with NOT. Here is an example:

This would produce:

The corresponding SQL statement is:

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE ((Not (Employees.Department)="Corporate"));

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. To get a list of records where the student's email address is not specified, you can use a statement as follows:

SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
WHERE EmailAddress IS NULL

In the same way, to validate that something is not null, you can use the expression IS NOT NULL.To see a list of only the students whose records have an email address, you can use a statement as follows: 

SELECT LastName, FirstName, State, EmrgName, EmrgPhone FROM Students WHERE EmrgPhone IS NOT NULL

 

Pattern Operator: LIKE

 

Introduction

Most or all of the criteria we have specified with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact desired value of records but you want to specify some type of approximation. To do this, you use the LIKE operator. If you are visually creating the statement, in the Select Query window, click the Criteria box corresponding to the column on which the condition would be applied and type. In a SQL statement, the LIKE operator is used in a formula as follows:

Expression LIKE pattern

The Expression factor is the expression that will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName)="Scott"));

is equivalent to

SELECT Employees.DateHired, Employees.FirstName, 
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "Scott"));

This would produce:

The idea of using a LIKE operator is to give an approximation of the type of result you want. The available wildcards to se with the LIKE operator are:

LIKE Any Character *

 

If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in S*, the condition would consist of finding any string that starts with S. Imagine that you want to create a list of employees whose last names start with S. You would type the condition as LIKE "S*" (or LIKE 'S*'). To do this visually, in the Criteria field of the column, type the condition. Here is an example:

The SQL statement is this query is:

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "S*"));

This would produce:

You can negate this condition by preceding it with NOT. Here is an example:

The SQL statement of this query is:

SELECT Employees.DateHired, Employees.FirstName
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Not Like "S*"));

This would produce:

As you can see, this results in the list of employees whose last names don't start with S.

When you precede the * character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the * symbol. For example, if you have some first names that start with Paul in a list but you don't remember the end of the name you are looking for, to create the list, you can specify that the first name would start with Paul and end with whatever. In this case, you would use Paul* as follows:

The corresponding SQL statement is:

SELECT 	Employees.DateHired, Employees.FirstName, 
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.FirstName) Like "Paul*"));

This would produce:

Instead of ending a letter or a group of letters with *, you can begin them with *. An example would be LIKE "*son". In this case, all strings that end with son, such as Johnson or Colson, would be considered.

If you remember neither the beginning nor the end of a string you want to search for but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with * and end it with *. An example would be LIKE "*er*". In this case, all strings that include er anywhere inside, such as Berg or Merck, would be considered.

Like the other SQL statements, you can also negate this one.

Practical Learning: Hiding a Column in a Query

1. In the Database window, click Tables and click Employees
2. On the main menu, click Insert -> Queries
3. In the New Query dialog box, double-click Design View
4. In the Employees list, double-click LastName, FirstName, Title, and CanCreateNewAccount
5. In the Criteria box corresponding to the Title column, type LIKE "*Manager*" and press Enter
6. In the Sort field of the LastName column, select Ascending
7. Save the query as Managers
    
   
8. Switch to the Datasheet View to see the result:
    
   
9. Close the query

LIKE a Range of Characters []

The * wildcard is used to make it precede or succeed a specific character or a group of characters, that is, any character. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to get a list of employees whose last names start with letters between D and M, you would specify the criterion as LIKE "[D-M]*". Here is an example:

The SQL statement of this query is:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.FirstName) Like "[D-M]*"));

This would produce:

Once again, remember that you can negate this expression by preceding it with NOT.

LIKE an Unknown Character ?

Imagine that you know a certain pattern in the string but you know you are forgetting only one letter. For example, if you know that the employee's first name sounds like Saul, Haul, or Paul. In other words, you don't know for sure what the first letter is but you are sure about the other characters. To match a character in a string, you can use the ? symbol as the wild card. Here is an example:

The corresponding SQL statement is:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.FirstName) Like "?aul"));

This would produce:

Notice that this produces the employees whose first name ends with aul regardless of the first character.