LIKE and NOT LIKE

Search for a pattern in a property using the format <property> (NOT) LIKE <pattern>.

Tip:    To search for a string that includes an apostrophe literal, use the character twice. For example, if you are trying to do a pattern match for "Jonathan's folder", you would use two apostrophes: WHERE SI_NAME LIKE 'Jonathan''s%'

The pattern can include the following wildcard characters.

Wildcard character	Description	Example
%	Any string of zero or more characters	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character	WHERE firstname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE lastname LIKE '[C-P]arsen' finds last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE lastname LIKE 'de[^l]%' all last names beginning with de and where the following letter is not l.

You can use a wildcard character as a literal character by enclosing it in brackets.

Symbol	Meaning
LIKE '5[%]'	5%
LIKE '[_]n'	_n
LIKE '[ [ ]'	[
LIKE 'abc[_]d%'	abc_d and abc_de

You can also use LIKE to do a search that is case-insensitive.

Example

To finds objects whose description has "report" in it, regardless of the case, use the following syntax:

SELECT SI_ID, SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_DESCRIPTION LIKE '%report%'

Example

To create a case-sensitive query, enclose the characters you are searching for with brackets:

SELECT SI_ID, SI_NAME, SI_DESCRIPTION FROM CI_INFOOBJECTS WHERE SI_DESCRIPTION LIKE '%[R][E][P][O][R][T]%'

For more information on using the LIKE operator, see Lesson 2d: Searching for reports.