LIKE

LIKE allows pattern matching within character-based column data.

WHERE expr [ NOT ] LIKE value

The value on the right side of a LIKE expression must be a simple string constant, the USER keyword, or (outside a stored procedure) a dynamic parameter supplied at runtime, indicated by a question mark “?”. Dynamic parameters are not supported within SQL Editor, only in application code.

Use the percent sign “%” in the comparison value as many times as desired to match zero or more characters in the column values. Use the underscore “_” to match any one character. If you need to match one of these wildcard symbols as a literal character, use a back slash in front of the symbol to mark the symbol as a literal character.

Table 44

Wildcard: matches zero or more characters.

Underscore “_”

Wildcard: matches any single character.

Back slash “\”

Flags the following wildcard character as a literal character, indicating that you want to match the actual wildcard character itself. To match a back slash, enter two back slashes. For example, to match “%“, the pattern should specify “\%0”.

Two single quotes “‘’”

Two single quotes without a space in between them must be used to match a single quote that is part of the result string. For example, if a row in the database contains the value “Jim’s house,” you can match this pattern by specifying LIKE ‘Jim’’s house’ in the WHERE clause. A double-quote (") is not a special character within the pattern string, and can be used like any letter or digit.

Examples

This example matches all column values that are five characters long and have abc as the middle three characters:

SELECT Building_Name FROM Room WHERE Building_Name LIKE '_abc_'

============

This example matches all column values that contain a back slash:

SELECT Building_Name FROM Room where Building_Name LIKE '%\\%'

============

This example matches all column values except those that begin with a percent sign:

SELECT Building_Name FROM Room where Building_Name NOT LIKE '\%%'

============

This example matches all column values that contain one or more single-quotes:

SELECT Building_Name FROM Room where Building_Name LIKE '%''%'

============

This example matches all column values where the second character is a double-quote:

SELECT Building_Name FROM Room where Building_Name LIKE '_"%'

============

This example creates a stored procedure that returns any rows where the Building_Name column contains the characters stored in the input variable :rname and where the Type column contains the characters stored in the input variable :rtype.

CREATE PROCEDURE room_test(IN :rname CHAR(20), IN :rtype CHAR(20))

RETURNS(Building_Name CHAR(25), “Type” CHAR(20));

BEGIN

DECLARE :like1 CHAR(25);

DECLARE :like2 CHAR(25);

SET :like1 = '%' + :rname + '%';

SET :like2 = '%' + :rtype + '%';

SELECT Building_Name, “Type” FROM Room WHERE Building_Name LIKE :like1 AND “Type” LIKE :like2;

END;

Note that the following statement, if placed in the stored procedure above, generates a syntax error because of the expression on the right side of the LIKE operator. The right side must be a simple constant:

Incorrect syntax:

SELECT Building_Name, “Type” from Room where Building_Name LIKE '%' + :rname + '%';