Home

Filtering by Patterns

Fundamentals of Patterns

Introduction

Most or all of the criteria we have specified so far with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact value in records but you want to specify some type of approximation. To let you do this, the SQL provides the LIKE operator.

Most or all of the criteria we have specified so far with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact value in records but you want to specify some type of approximation. To let you do this, the SQL provides the LIKE operator.

If you are visually creating the condition, in the Criteria text box that corresponds to the column on which the condition must apply, type the LIKE expression. Its formula is:

expression LIKE pattern

The expression is usually the name of a column. The pattern can be a value to be found in the expression column.

In most cases, the expression is preceded by the WHERE operator. The operation performed by LIKE deals with string-based columns. The LIKE operator is used to perform a comparison to find out if the value of a field (the expression) exactly or approximately matches a pattern. If the operation is valid, it produces a Boolean value as True or False. You can then use the result as you see fit.

Filtering by a Pattern

Practical LearningPractical Learning: Introducing Patterns

  1. Start Microsoft Access and open the StatesStatistics3 database from the previous lesson
  2. On the Ribbon, click Create and click Query Design
  3. In the Show Table, click States, click Add, and click Close
  4. In the top list, double-click StateName, AreaSqrKms, AdmissionUnionDate, and Region
  5. To see the result, in the Results section of the Ribbon, click the Run button Run
  6. If necessary, on the Ribbon, click Home.
    Change the following characteristics:
    Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
    Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
    Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
    Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)
  7. On the Ribbon, click the View button to switch the query to the Design View

A Pattern that Exactly Matches a Value

The most basic comparison performed on a string-based field is to find out whether it is equal to a certain string. We already know that this operation can be performed using the = operator. Here is an example:

SELECT PropertyNumber, 
       City, 
       PropertyType, 
       PropertyNumber, 
       City, 
       PropertyType, 
       Condition, 
       Bedrooms, 
       Bathrooms, 
       FinishedBasement, 
       Stories, 
       MarketValue
FROM Properties
WHERE  PropertyType = "single family";

The LIKE operator can be used to perform the same operation. To do this, use the following formula:

WHERE column-name LIKE value

Here is an example:

SELECT PropertyNumber,
       City,
       PropertyType,
       PropertyNumber,
       City, 
       PropertyType, 
       Condition, 
       Bedrooms,
       Bathrooms,
       FinishedBasement, 
       Stories, 
       MarketValue
FROM Properties
WHERE  PropertyType LIKE "single family";

This and the previous code produce the same result:

A Pattern that Exactly Matches a Value

To make the operation easier to read, you can put it in parentheses. The above code can be written as follows:

SELECT PropertyNumber, 
       City, 
       PropertyType, 
       PropertyNumber, 
       City, 
       PropertyType, 
       Condition, 
       Bedrooms, 
       Bathrooms, 
       FinishedBasement, 
       Stories, 
       MarketValue
FROM Properties
WHERE  (PropertyType LIKE "single family");

Practical LearningPractical Learning: Creating a Pattern that Matches a Value

  1. In the bottom part of the window, click the Criteria box for Region and type LIKE 'pacific'
  2. To see the result, in the Results section of the Ribbon, click the Run button Run

    Introducing Patterns

  3. On the Ribbon, click the View button Design View to switch the query to the Design View

Negating a Pattern

As done with all Boolean operations, there are ways you can negate a LIKE operation. Probably the easiest way is to precede the expression with the NOT operator. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE Region NOT LIKE 'New England';

If you precede the expression with NOT, it is recommended that you put the section after NOT between parentheses.

As an alternative, in most cases, you can also precede the LIKE keyword with NOT. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE NOT Region Like 'New England';

You can also negate a negation. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE NOT States.Region NOT Like 'New England';

If you negate a negation, you can get the result as though none of both negations was used.

Practical LearningPractical Learning: Negating a Pattern

  1. In the bottom part of the window, change the criterion as Not (Like 'pacific')
  2. To see the results, on the status bar, click the Datasheet View button

    Negating a Pattern

  3. After viewing the results, on the status bar, click the Design View button Design View
  4. Delete the criterion expression

Sorting Records

If you are visually creating a query, you can sort the records using the Sort combo box in the bottom side of the window and for the field of your choice.

Patterning by a Wildcard

Matching a Character

The idea of using a LIKE operator is to give an approximation of the value you want to compare to a field. To formulate this approximation, you use some specific symbols referred to as wildcards. They are combined with the LIKE operator.

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 n*, the condition would consist of finding any string that starts with that letter, in this case n. If you want to apply the search to multiple occurrences of a character, use as many combinations of the letter delimited by *.

Remember that you can negate a LIKE condition by preceding it with NOT. Here is an example:

Practical LearningPractical Learning: Matching Any Character

  1. To get the list of states whose names start with n, in the Criteria box for the StateName column, type LIKE 'n*'
  2. To see the result, On the Ribbon, click the Run button Run

    Matching Any Character

  3. After seeing the results, on the Ribbon, click the View button Design View
  4. To get the list of states that include a twice in their names, change the expression as follows: Like '*a*a*'
  5. To see the result, On the Ribbon, click the Run button Run

    Matching Any Character

  6. After seeing the results, on the Ribbon, click the View button Design View
 
 
 

Matching a Single Character

The * symbol on a LIKE operation is used to find many characters on the left or the right side of its accompanying character. If you want to find only one character, use the ? wildcard. For example, if you don't know by what character a string starts but know one or more characters after it, you can use ? followed by a sub-string. An example would be LIKE '?ane'. This would produce such strings as Cane, Lane, or Sane.

If you don't know by what character a string starts, know the second character, but don't remember the rest of the characters, you can combine the ? and the * symbols.

Remember that you can negate a LIKE condition by preceding it with NOT.

Practical LearningPractical Learning: Matching a Single Character

  1. To get the list of states whose names start with any letter, followed by i, and followed by any number of letters, change the Criteria box of the StateName to Like "?i*"
  2. To see the result, On the Ribbon, click the Run button

    Matching Any Character

  3. After seeing the results, display the SQL View
  4. To get the list of states that start with any letter followed by a, but also includes a and s separaterd by any letter, change the statement as follows:
    SELECT States.StateName,
           States.AreaSqrKms,
           States.AdmissionUnionOrder,
           States.Region
    FROM States
    WHERE StateName Like "?a?s*";
  5. Preview the results

    Matching Any Character

  6. Switch to the Design View

Matching Specific Characters

Instead of one specific character, you may want to match many. To do this, use the square brackets [] as the placeholder. Inside the brackets, enter the characters separated by commas. Outside the brackets, apply the * wildcard on either or both sides.

Practical LearningPractical Learning: Matching Specific Characters

  1. To get the list of states whose names start with letters F, C, T, P, or O, change the Criteria of StateName as follows: Like '[f, c, t, p, o]*'
  2. Preview the results:

    Matching Any Character

  3. Return to the Design View

Matching a Range of Alphabetic Characters

A range of alphabetic characters is a list of letters that consecutively follow each other in the alphabet. To let you match the letters that belong to the same range in the alphabet, include the square-brackets [] in the the LIKE string. Inside the brackets, the first letter, followed by -, and followed by the last letter of the range. On any or both parts of the square-brakets, apply the * wildcard using its rules. Consider the following example:

SELECT AtomicNumber, 
       Symbol,
       ElementName,
       AtomicWeight
FROM Elements
WHERE ElementName Like '[c-g]*';

In this case, the result will include all chemical elements whose names start with C, D, E, F, or G. This would produce:

Matching a Range of Alphabetic Characters

Practical LearningPractical Learning: Matching a Range of Characters

  1. To get the list of states whose names start with letters in the range C to I, set the Criteria of StateName as follows: Like '[c-i]*'
  2. To see the result, On the Ribbon, click the Run button

    Matching a Range of Characters

  3. Return to the Design View
  4. To get a list of states whose names end with letters in the range A to I, change the StateName criterion as follows: Like '*[a-i]'
  5. To see the result, On the Ribbon, click the Run button

    Matching a Range of Characters

  6. Return to the Design View

Matching a Sub-String

Besides a single character, the * wildcard can be applied to a group of letters, called a sub-string. The rules are the same for a single character

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM   States
WHERE  Region Not Like '*north*';

Practical LearningPractical Learning: Matching a Sub-String

  1. To get the list of states that include two consecutive s in their names, change the expression as follows: Like '*ss*'
  2. Preview the results in the Datasheet View

    Matching a Sub-String

  3. Display the Design View of the query
  4. To get the list of states that include the ia sub-string in their names, change the criterion as follows: LIKE '*ia*'
  5. Preview the results in the Datasheet View

    Matching a Sub-String

  6. Display the Design View of the query
  7. To get the list of regions that include NORTH in their names, change the criterion as follows: LIKE '*north*'
  8. Preview the results in the Datasheet View

    Matching Any Character

  9. Display the Design View of the query
  10. In the Region column, delete the criterion

Negating a Wildcard Expression

As opposed to considering the characters that are in a specific range, to specify a character, some characters, a range of characters, or a sub-string that must not be considered, you have two options. As seen already, you can precede the LIKE expression with the NOT operator. As an alternative, use the ! character inside the square brackets but before the character(s).

Practical LearningPractical Learning: Negating a Pattern

  1. To get the list of states whose names don't start with letters in the range C to I, set the Criteria of StateName as follows: Like '[!c-i]*'
  2. To see the result, On the Ribbon, click the Run button

    Negating a Wildcard Expression

  3. After seeing the results, display the Design View of the query
  4. To get a list of states whose names don't end with letters in the range A to I, change the StateName criterion as follows: Like '*[!a-i]'
  5. To see the result, On the Ribbon, click the Run button

    Negating a Wildcard Expression

  6. Display the Deisgn View of the query
  7. To get the list of states whose names don't start with letters in the range C to M, change the Criteria statement as follows: Like '[!c-m]*'
  8. Preview the results in the Datasheet View

    Negating a Wildcard Expression

  9. Return to the Design View and delete the criterion

Matching a Date/Time

If the value you want to find is a date, include it between two # signs.

Practical LearningPractical Learning: Matching a Date Value

  1. Click the Criteria box that corresponds to the AdmissionUnionDate filed and type Like #11/2/1889#
  2. To see the result, On the Ribbon, click the Run button

    Matching a Date Value

  3. Close Microsoft Access
  4. When asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2008-2016, FunctionX, Inc. Next