LIKE Predicate
The LIKE predicate performs pattern matching for the character data types (char, varchar, c, and text) and Unicode data types (nchar and nvarchar).
The LIKE predicate has the following syntax:
expression [NOT] LIKE pattern [ESCAPE escape_character]
The expression can be a column name or an expression containing string functions.
The pattern parameter will usually be a string literal but could be an arbitrary string expression. The pattern-matching (wild card) characters are the percent sign (%) to denote 0 or more arbitrary characters, and the underscore (_) to denote exactly one arbitrary character.
The LIKE predicate does not ignore the trailing blanks present with a char or nchar data type. If you are matching a char value (that is padded with blanks when it is inserted) or if the value has trailing blanks that were entered by the user, include these trailing blanks in your pattern. For example, if searching a char(10) column for rows that contain the name harold, specify the trailing blanks in the pattern to be matched:
name like 'harold '
Four blanks are added to the pattern after the name to include the trailing blanks.
Because blanks are not significant when performing comparisons of c data types, the LIKE predicate returns a correct result whether or not trailing blanks are included in the pattern.
If the ESCAPE clause is specified, the escape character suppresses any special meaning for the following character, allowing the character to be entered literally. The following characters can be escaped:
• The pattern matching characters % and _.
• The escape character itself. To enter the escape character literally, type it twice.
• Square brackets [ ]. Within escaped square brackets ( [ and ] ), a series of individual characters or a range of characters separated by a dash (-) can be specified.
The following examples illustrate some uses of the pattern matching capabilities of the LIKE predicate.
To match any string starting with a:
name LIKE 'a%'
To match any string starting with A through Z:
name LIKE '\[A-Z\]%' ESCAPE '\'
To match any two characters followed by 25%:
name LIKE '__25\%' ESCAPE '\'
To match a string starting with a backslash:
name LIKE '\%'
Because there is no ESCAPE clause, the backslash is taken literally.
To match a string starting with a backslash and ending with a percent:
name LIKE '\\%\%' ESCAPE '\'
To match any string starting with 0 through 4, followed by an uppercase letter, then a [, any two characters and a final ]:
name LIKE '\[01234\]\[A-Z\][__]' ESCAPE '\'
To detect names that start with S and end with h, disregarding any leading or trailing spaces:
TRIM(name) LIKE 'S%h'
To detect a single quote, repeat the quote:
name LIKE ''''