A single-character wildcard

The statements in the following query illustrate the use of a single-character wildcard in a WHERE clause. Further, they demonstrate a query on a table that is not in the current database. The stock table is in the database sloth. Besides being outside the current demonstration database, sloth is on a separate database server called meerkat.

For more information, see Access and modify data in an external database and the HCL Informix® Guide to SQL: Syntax.

Figure 1. Query

SELECT stock_num, manu_code, description, unit_price 
   FROM sloth@meerkat:stock
   WHERE manu_code LIKE '_R_'
       AND unit_price >= 100
   ORDER BY description, unit_price;

SELECT stock_num, manu_code, description, unit_price
   FROM sloth@meerkat:stock
   WHERE manu_code MATCHES '?R?'
      AND unit_price >= 100
   ORDER BY description, unit_price;

Each statement in the query retrieves only those rows for which the middle letter of the manu_code is R, as the result shows. The comparison '_R_' (for LIKE) or '?R?' (for MATCHES) specifies, from left to right, the following items:

Any single character
The letter R
Any single character

Figure 2. Query result

stock_num manu_code description     unit_price

      205 HRO       3 golf balls       $312.00
        2 HRO       baseball           $126.00
        1 HRO       baseball gloves    $250.00
        7 HRO       basketball         $600.00
      102 PRC       bicycle brakes     $480.00
      114 PRC       bicycle gloves     $120.00
        4 HRO       football           $480.00
      110 PRC       helmet             $236.00
      110 HRO       helmet             $260.00
      307 PRC       infant jogger      $250.00
      306 PRC       tandem adapter     $160.00
      308 PRC       twin jogger        $280.00
      304 HRO       watch              $280.00