Using wildcard characters as literal characters

You can search for wildcard characters by escaping them and searching for them as literals. There are two ways to use the wildcard characters as literals in a like match string: square brackets and the escape clause. The match string can also be a variable or a value in a table that contains a wildcard character.

Square brackets (Transact-SQL extension)

Use square brackets for the percent sign, the underscore, and right and left brackets. To search for a dash, rather than using it to specify a range, use the dash as the first character inside a set of brackets.

**Table 2-3: Using square brackets to search for wildcard characters**
like clause	Searches for
like "5%"	5 followed by any string of 0 or more characters
like "5[%]"	5%
like "_n"	an, in, on, and so forth
like "[_]n"	_n
like "[a-cdf]"	a, b, c, d, or f
like "[-acdf]"	-, a, c, d, or f
like "[ [ ]"	[
like "[ ] ]"	]

escape clause (SQL-compliant)

Use the escape clause to specify an escape character in the like clause. An escape character must be a single-character string. Any character in the server’s default character set can be used.

**Table 2-4: Using the escape clause**
like clause	Searches for
like "5@%" escape "@"	5%
*like "_n" escape ""*	_n
like "%80@%%" escape "@"	String containing 80%
*like "_sql*%" escape ""**	String containing _sql*
like "%#####_#%%" escape "#"	String containing ##_%

An escape character is valid only within its like clause and has no effect on other like clauses in the same statement.

The only characters that are valid following an escape character are the wildcard characters ( _ , % , [ , ] , and [^] ), and the escape character itself. The escape character affects only the character following it. If a pattern contains two literal occurrences of a character that happens to be an escape character, the string must contain four consecutive escape characters (see the last example in Table 2-4). Otherwise, Adaptive Server raises a SQLSTATE error condition and returns an error message.

Specifying more than one escape character raises a SQLSTATE error condition, and Adaptive Server returns an error message:

like "%XX_%" escape "XX"
like "%XX%X_%" escape "XX"