InterBase Quick Start: Part IV - Pattern Matching

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.

Using 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:

   

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:

   

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:

   

Using 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:

   

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:

   

Using 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:

   

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:

• Testing for an Unknown Value