Select a string of characters: LIKE '%abc%'

You can select rows containing a string of characters that might be part of a word or number you know exists in the data. In the following example, WHERE ADDRESS LIKE '%NY' means, "Where the address ends with 'NY' with anything before that."  The percent sign (%) can stand for anything--any number of preceding characters, or none.

This query:

SELECT NAME, ADDRESS
FROM Q.APPLICANT
WHERE ADDRESS LIKE '%NY'

Produces this report:

NAME      ADDRESS
--------- -----------------
JACOBS    POUGHKEEPSIE, NY
REID      ENDICOTT, NY
LEEDS     EAST FISHKILL, NY

When using LIKE to search for data with a specific ending, be aware of the data type of the column you are searching. If the column has a fixed width and the data in the column varies in width, add blanks to the character string to match the blanks in the column data.

For example, if the ADDRESS column in the example has a data type of CHAR(17), the width of the column is fixed with blanks filling the space where the data is not as wide as the column. Searching with an ending character string requires that you anticipate, and search for, the string with every possible number of trailing blanks that could be encountered in the data.

If the ADDRESS column has a data type of VARCHAR, the width of the column varies with the data in it, because blanks are not appended to the data. In the database, no blanks follow the data in each row of the column.

Example:

Select everyone whose name begins with W:

SELECT ID, NAME
FROM Q.STAFF
WHERE NAME LIKE 'W%'