Skip to main content
Engineering LibreTexts

6.2: Query Operators

  • Page ID
    15525
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    We present 2 more operators: LIKE which is used for pattern matching of text values and IN which is used to test for inclusion in a set.

    Like

    Sometimes we need to get information based on partial information. Consider someone using the University database and wanting to find courses where the course description contains the word “computer”. To find courses matching this criterion we can use the Like operator where we specify an appropriate pattern. These patterns are defined using one or more wildcard characters. By default our MS Access databases use the ANSI-89 standard for special wildcard characters.

    Note

    At some point you may want to investigate the more recent ANSI-92 standard for wildcards. You can change the standard your database is using by examining and changing the MS Access Options for Object Designers/Query Design.

    The ANSI-89 wildcard characters are:

    Table \(\PageIndex{1}\): ANSI-89 wildcard characters.
    Wildcard Character Matching criteria Example
    *

    Matches any number of characters

    Like “1*” matches all text strings that start with “1”

    ?

    Matches any single character

    Like “a?c” matches “aac”, “abc”, “acc”, etc. but does not match longer strings such as “aacc” or “xabc”

    #

    Matches any single numeric character

    Like “b#b” would match “b2b” and “b7b” but not “bam”

    []

    Matches any single character within the brackets

    Like “j[ai]m” matches “jim” and “jam” but not “jaim”

    !

    Used with [] when you do not want to match any of the enclosed characters

    Like “b[!ao]b” matches “bim” and “bub” but not “bam” or “bob”

    -

    Used with [] to specify a range of matching characters (given in ascending sequence)

    Like “b[0-9]b” would match to “b2b” but not to “bam” Like “b[a-c]b” would match “bab”, “bbb”, and “bcb”

    Example

    To list courses where the description begins with “This course” you need a pattern where you specify that a text value begins with “This course” which can be followed by anything else: “This course*” .

    And so in QBE you enter the criteria for title: Like “This course*”:

    Using LIKE.
    Figure \(\PageIndex{1}\): Using LIKE.

    IN

    The IN operator can be used if you need to determine if a field value is in specific list of values. The list of values is a comma-separated list enclosed in parentheses; for example (1, 3, 6).

    Example

    To list those courses offered by the Physics, Statistics and Mathematics departments you need a list of values: (“PHYS”, “STAT”, “MATH”)

    Using QBE we code IN (“PHYS”, “STAT”, “MATH”) in the criteria line:

    Using IN.
    Figure \(\PageIndex{2}\): Using IN.

    Note

    Using IN is equivalent to using three simple logical expressions that are ORed, and is a convenient way of expression if there are several values in the list:

    IN vs OR.
    Figure \(\PageIndex{3}\): IN vs OR.

    Exercises

    Develop queries to:

    Exercise \(\PageIndex{1}\)

    List courses offered by Physics or Applied Computer Science where the course description contains the word computer.

    Exercise \(\PageIndex{1}\)

    List courses where the course description contains the word computer but where the course is not offered by the Applied Computer Science department.

    Exercise \(\PageIndex{1}\)

    List courses where the credit hours are 1, 3, 6 or 9.

    Exercise \(\PageIndex{1}\)

    List courses where the credit hours are not 1, not 3, not 6, and not 9.


    This page titled 6.2: Query Operators is shared under a CC BY-NC-SA license and was authored, remixed, and/or curated by Ron McFadyen.

    • Was this article helpful?