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. If data is found that matches a given pattern, the row is retrieved.

Wildcards: Use a percent sign (%) to match zero or more characters. Use an underscore (_) to match a single character.

The table below gives examples of some common patterns. Only CONTAINING is not case sensitive.

WHERE	Matches
last_name LIKE '%q%'	Last names containing at least one “q”.
last_name STARTING WITH 'Sm'	Last names beginning with the letters “Sm.”
Last_name CONTAINING 'q'	Last name contains at least one “q”, either uppercase or lowercase.
Last name BETWEEN 'M' AND 'T'	Last name beginning with letters “M” through “S”.

Pattern Matching Examples

Find What’s LIKE a Value

1. LIKE is case sensitive and takes wildcards. Execute this 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 result set should look like this:

   

2. Now enter the following SELECT statement to find employees whose last names begin with “M”, have exactly two more characters and then a “D”, followed by anything else in the remainder of the name:

    SELECT last_name, first_name, emp_no FROM Employee 
         WHERE last_name LIKE 'M__D%'
   

   This returns:

   MacDonald            Mary S.             85
   

   but it would ignore names like McDonald.

3. The “%” 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%'
   

   This returns:

   

Find Things STARTING WITH

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

1. Execute the following statement to retrieve two employee last names that start with “Le”:

   SELECT last_name, first_name FROM Employee 
         WHERE last_name STARTING WITH 'Le'
   

   The result set is:

   

2. To negate the STARTING WITH operator, precede it with the logical operator NOT. (Note: That is a “one” in the quotes at the end of the statement.)

   SELECT dept_no, department, location from department  
         WHERE dept_no NOT starting with '1'
   

   This query should return the following 10 rows:

   

Find Something CONTAINING a Value

The CONTAINING operator is similar to STARTING WITH, except it matches strings containing the specified string anywhere within the string. CONTAINING is not case sensitive and does not support wildcards.

1. Execute the following statement to find last names that have a “g” or “G” anywhere in them.

    SELECT last_name, first_name FROM Employee
     WHERE last_name CONTAINING 'G'
   

   You should get the following result set:

   

2. Now execute the same query, except substitute a lower-case “g.” You should get exactly the same result set.

   SELECT last_name, first_name FROM Employee
        WHERE last_name CONTAINING 'g'
   

Advance To:

• Testing for an Unknown Value