Retrieving Information from a Table › Retrieving Selected Rows › Character string comparisons

Character string comparisons

Using LIKE

You can compare an alphanumeric field to a mask (pattern) that contains alphanumeric characters and wild card symbols. For example, you might want to retrieve information about jobs that have CLERK in the job title.

To code the pattern, use the wild card symbols described below. If the pattern contains embedded blanks, enclose it in single quotation marks.

Some examples using these symbols appear below:

Note: To improve CA OLQ's performance, use conditional operators, rather than LIKE, to perform character string comparisons. For example, use where firstname = 'b' to retrieve all employees whose first name begins with B.

Example 1— Using a % sign

List the names of all employees with initials JG:

select firstname, lastname
  from emp
  where firstname like &xq.j%'
    and lastname like &xq.g%' ! display

                                   EMP REPORT
                                    mm/dd/yy

                   FIRSTNAME                      LASTNAME
                   ──────────                  ───────────────
                   JENNIFER                    GARFIELD
                   JAMES                       GALLWAY
     END OF REPORT

Example 2— Using two % signs

List all employees whose name contains the letter Z:

select lastname
  from emp
  where lastname like &xq.%z%' ! display

                                   EMP REPORT
                                    mm/dd/yy

                                    LASTNAME

                                 HEAROWITZ
                                 PAPAZEUS
                                 ZEDI
     END OF REPORT

Example 3— Using an underscore (_)

List all employees whose name contains 5 letters, beginning with J:

select firstname, lastname
  from emp
  where firstname like &xq.j____' ! display

                                   EMP REPORT
                                    mm/dd/yy

                   FIRSTNAME                      LASTNAME
                   ──────────                  ───────────────
                   JULIE                       JENSEN
                   JAMES                       JACOBI
                   JAMES                       GALLWAY
     END OF REPORT