Using CONTAINING

Go Up to Using Comparison Operators in Expressions

CONTAINING tests to see if an ASCII string value contains a quoted ASCII string supplied by the program. String comparisons are case-insensitive; “String”, “STRING”, and “string” are equivalent values for CONTAINING. Note that for Dialect 3 databases and clients, the strings must be enclosed in single quotation marks. The complete syntax for CONTAINING is:

<value> [NOT] CONTAINING '<string>'

For example, the following cursor declaration retrieves the names of all employees whose last names contain the three-letter combination, “las” (and “LAS” or “Las”):

EXEC SQL
DECLARE LAS_EMP CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME CONTAINING 'las';

Use NOT CONTAINING to test for strings that exclude a specified value. For example, the following cursor declaration retrieves the names of all employees whose last names do not contain “las” (also “LAS” or “Las”):

EXEC SQL
DECLARE NOT_LAS_EMP CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME NOT CONTAINING 'las';

Tip:
CONTAINING can be used to search a Blob segment by segment for an occurrence of a quoted string.

Advance To:

• Using IN