Exact-text comparisons

The following examples include a WHERE clause that searches for exact-text comparisons by using the keyword LIKE or MATCHES or the equal sign (=) relational operator. Unlike earlier examples, these examples illustrate how to query a table that is not in the current database. You can access a table that is not in the current database only if the database that contains the table has the same ANSI compliance status as the current database. If the current database is an ANSI-compliant database, the table you want to access must also reside in an ANSI-compliant database. If the current database is not an ANSI-compliant database, the table you want to access must also reside in a database that is not an ANSI-compliant database.

Although the database used previously in this chapter is the demonstration database, the FROM clause in the following examples specifies the manatee table, created by the owner bubba, which resides in an ANSI-compliant database named syzygy. For more information on how to access tables that are not in the current database, see the HCL OneDB™ Guide to SQL: Syntax.

Each statement in the following query retrieves all the rows that have the single word helmet in the description column, as the result shows.

Figure 1: Query

SELECT stock_no, mfg_code, description, unit_price
   FROM syzygy:bubba.manatee
   WHERE description = 'helmet'
   ORDER BY mfg_code;

SELECT stock_no, mfg_code, description, unit_price
   FROM syzygy:bubba.manatee
   WHERE description LIKE 'helmet'
   ORDER BY mfg_code;

SELECT stock_no, mfg_code, description, unit_price
   FROM syzygy:bubba.manatee
   WHERE description MATCHES 'helmet'
   ORDER BY mfg_code;

The results might look like the following figure.

Figure 2: Query result

stock_no mfg_code  description     unit_price

    991 ABC       helmet             $222.00
    991 BKE       helmet             $269.00
    991 HSK       helmet             $311.00
    991 PRC       helmet             $234.00
    991 SPR       helmet             $245.00