[NOT] LIKE (TSQL Operator)

Checks if an expression matches a specified pattern.

Syntax

expression [ NOT ] LIKE pattern [ESCAPE escape_char]

Arguments

expression

is an expression of type varchar.

pattern

is the pattern that expression is searched for. It can include wildcard symbols. It is of type varchar.

escape_char

if this character precedes a wildcard symbol, the latter will be interpreted as a regular character. It is of type varchar.
It must be a single character. There is no default value for escape_char.

Return Types

Returns a result of type bool.

Remarks

A pattern can include the following wildcards symbols:

Wildcard Symbol	Description	Example
%	Any string of zero or more characters.	The pattern '%hello%' matches any string that contains the substring 'hello'.
_ (underscore)	Any single character.	The pattern 'lecture_' matches 'lecture0', 'lecture1', 'lectureA', 'lectureB', etc.
[]	Any single character within the specified list ([abcd]) or range ([a-d]).	The pattern '[bpt]ear' matches 'bear', 'pear', 'tear'. The pattern '[b-t]ear' matches 'bear', 'cear', 'dear', ..., 'tear'.
[^]	Any single character not within the specified list ([^abcd]) or range ([^a-d]).	The pattern '[^bpt]ear' matches 'aear', 'cear', etc, but not 'bear', 'pear', 'tear'. The pattern '[^b-t]ear' matches 'aear', 'uear', 'vear', etc, but not 'bear', 'cear', 'dear', ..., 'tear'.

The trailing blanks in expression are discarded (see example).

LIKE comparisons are affected by collation.

NOT LIKE returns the negated result of LIKE.

Examples

PRINT 'Matches any string that begins with a letter (uppercase or lowercase), or an underscore.'
PRINT 'a' LIKE '[a-zA-Z_]%';
PRINT 'abc' LIKE '[a-zA-Z_]%';
PRINT 'Abc' LIKE '[a-zA-Z_]%';
PRINT '_bc' LIKE '[a-zA-Z_]%';

PRINT '';
PRINT 'Returns true, because trailing blanks in expression are discarded.'
PRINT 'hello    ' LIKE 'hello';

PRINT '';
PRINT 'To use a wildcard symbol as a normal character, enclose it between brackets'
PRINT 'lecture_123' LIKE 'lecture[_][0-9][0-9][0-9]'; -- underscore is used as normal character

PRINT '';
PRINT 'or escape it with an escape character.'
PRINT 'lecture_123' LIKE 'lecture\_[0-9][0-9][0-9]' ESCAPE '\'; -- underscore is used as normal character

PRINT '';
PRINT 'The result depends on collation.'
PRINT 'hello' LIKE 'HELLO'; -- default server collation is fr_ci_ai
PRINT 'hello' LIKE 'HELLO' COLLATE fr_cs_as;

The result is:

true
true
true
true

Returns true, because trailing blanks of expression are discarded.
true

To use a wildcard symbol as a normal character, enclose it between brackets
true

or escape it with an escape character.
true

The result depends on collation.
true
false