Using QMF

Narrowing row selection using selection symbols

To select rows using selection symbols, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols.

• Use an underscore (_) to fill in for one character.
• Use the percent sign (%) to fill in for zero or more characters.
• Any other character represents a single occurrence of itself.

For example, this query selects the rows for employees whose names end in SON.

SELECT NAME
  FROM Q.STAFF
  WHERE NAME LIKE '%SON'

This query selects the rows for employees whose names are five characters long and end in ES.

SELECT NAME
  FROM Q.STAFF
  WHERE NAME LIKE '___ES'

(The line '___ES' includes three underscores.)

+--------------------------------------------------------------------------------+
|   NAME                                                                         |
|   ---------                                                                    |
|   HANES                                                                        |
|   JAMES                                                                        |
|   JONES                                                                        |
+--------------------------------------------------------------------------------+

You can use % more than once in an expression.

For example, the following query selects the rows for employees whose names contain an M and then an N. From the Q.STAFF sample table, this query selects MARENGHI, ROTHMAN, and MOLINARE.

WHERE NAME LIKE '78N%'

You can use the % and _ selection symbols in the same WHERE clause.

For example, the following query selects the rows for employees whose names have R as the second letter. From the Q.STAFF sample table, this query selects FRAYE and GRAHAM.

WHERE NAME LIKE '_R%'

You can use the NOT keyword with selection symbols to specify rows you do not want to select.

For example, the following query selects the rows for employees whose names do not begin with G.

WHERE NAME NOT LIKE 'G%'