WHERE clause with variable-length wildcard

The statements in the following query use a wildcard at the end of a string to retrieve all the rows where the description begins with the characters bicycle.

Figure 1. Query

SELECT stock_num, manu_code, description, unit_price 
   FROM stock
   WHERE description LIKE 'bicycle%'
   ORDER BY description, manu_code;

SELECT stock_num, manu_code, description, unit_price
   FROM stock
   WHERE description MATCHES 'bicycle*'
   ORDER BY description, manu_code;

Either statement returns the following rows.

Figure 2. Query result

stock_num manu_code description     unit_price

      102 PRC       bicycle brakes     $480.00
      102 SHM       bicycle brakes     $220.00
      114 PRC       bicycle gloves     $120.00
      107 PRC       bicycle saddle      $70.00
      106 PRC       bicycle stem        $23.00
      101 PRC       bicycle tires       $88.00
      101 SHM       bicycle tires       $68.00
      105 PRC       bicycle wheels      $53.00
      105 SHM       bicycle wheels      $80.00

The comparison 'bicycle%' or 'bicycle*' specifies the characters bicycle followed by any sequence of zero or more characters. It matches bicycle stem with stem matched by the wildcard. It matches to the characters bicycle alone, if a row exists with that description.

The following query narrows the search by adding another comparison condition that excludes a manu_code of PRC.

Figure 3. Query

SELECT stock_num, manu_code, description, unit_price 
   FROM stock
   WHERE description LIKE 'bicycle%'
      AND manu_code NOT LIKE 'PRC'
   ORDER BY description, manu_code;

The statement retrieves only the following rows.

Figure 4. Query result

stock_num manu_code description     unit_price

      102 SHM       bicycle brakes     $220.00
      101 SHM       bicycle tires       $68.00
      105 SHM       bicycle wheels      $80.00

When you select from a large table and use an initial wildcard in the comparison string (such as '%cycle'), the query often takes longer to execute. Because indexes cannot be used, every row is searched.