InterBase Quick Start: Part IV - Pattern Matching

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part IV - Using the WHERE Clause


Besides comparing values, search conditions can also test character strings for a particular pattern. You can use wildcards to define patterns:

  • % (a percent sign): Match zero or more characters.
  • _ (an underscore): Match a single character.

The table below provides examples of some common patterns. All conditions except CONTAINING are case sensitive.

WHERE Clause Result
WHERE last_name LIKE '%q%'

Last names that contain at least one q character.

WHERE last_name STARTING WITH 'Sm'

Last names that begin with the letters Sm.

WHERE Last_name CONTAINING 'q'

Last names that contain at least one q character (either uppercase or lowercase).

WHERE last_name BETWEEN 'M' AND 'T'

Last names that begin with letters alphabetically between (and including) M and S.

Image 025.jpgUsing LIKE

  1. LIKE is case sensitive and accepts wildcards. Enter the following statement to find all employees whose last name ends in an.
    SELECT last_name,
           first_name,
           emp_no
    FROM   Employee
    WHERE  last_name LIKE '%an'
    

    The image below shows the expected result:

    TutorialWhere5.png
  2. Now enter the following SELECT statement to find employees whose last names match the pattern:
    • Begin with an M character.
    • Followed by two characters.
    • Then a D character.
    • Followed any or none characters.
    SELECT last_name,
           first_name,
           emp_no
    FROM   Employee
    WHERE  last_name LIKE 'M__D%'
    

    The image below shows the expected result:

    TutorialWhere6.png
  3. The % wildcard matches zero or more characters. The following query returns rows for last names Burbank, Bender, and Brown.
    SELECT last_name,
           first_name,
           emp_no
    FROM   Employee
    WHERE  last_name LIKE 'B%r%'
    

    The image below shows the expected result:

    TutorialWhere7.png

Image 025.jpgUsing STARTING WITH

The STARTING WITH operator tests whether a value starts with a particular character or sequence of characters. STARTING WITH is case sensitive and does not support wildcard characters.

  1. Use the following statement to retrieve employee last names that start with Le:
    SELECT last_name,
           first_name
    FROM   Employee
    WHERE  last_name starting WITH 'Le'
    

    The image below shows the expected result:

    TutorialWhere8.png
  2. To negate the STARTING WITH operator, precede it with the logical operator NOT.
    SELECT dept_no,
           department,
           location
    FROM   Department
    WHERE  dept_no NOT starting WITH '1'
    

    The image below shows the expected result:

    TutorialWhere9.png

Image 025.jpgUsing CONTAINING

The CONTAINING operator finds results that contain a particular character or sequence of characters. CONTAINING is not case sensitive and does not support wildcards.

  1. Enter the following statement to find last names that contain a g or a G character.
    SELECT last_name,
           first_name
    FROM   Employee
    WHERE  last_name containing 'G'
    

    The image below shows the expected result:

    TutorialWhere10.png
  2. To confirm that the CONTAINING operator is not case sensitive, modify the previous query to specify a lowe-case g in the condition.
    SELECT last_name,
           first_name
    FROM   Employee
    WHERE  last_name containing 'g'
    

    You can see that the result is the same in both cases.

Advance To: