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 run time, indicated by a question mark. Dynamic parameters are not supported within SQL Editor, but rather only in application code.

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

Table 36

Special Characters

Percent sign “%”

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 quotation marks “''’”

Two single quotation marks with no space between them must be used to match a single quotation mark in 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-quotation mark in the pattern string is not a special character and can be used like any letter or digit.

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));

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;

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.

The following syntax is incorrect and will fail:

SELECT Building_Name, "Type" from Room WHERE Building_Name LIKE '%' + :rname + '%';