LIKE operator in SQL Server

It helps us to get the records for which column value character strings matches the specified pattern.
A pattern can have,

• Regular characters
• Regular characters with Wildcard characters

In the case of, during pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters facilitate us to get the character string which are closely similar to mentioned pattern or can be matched with arbitrary fragments of the character string.
 
Syntax
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
 
Arguments

• match_expression:- Is any valid SQL Server expression of character string data type.
• Pattern: - Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.

Wildcard character	Description
%	Any string of zero or more characters.
_ (underscore)	Any single character.
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

• escape_character:- Is any valid SQL Server expression of any of the data types of the character string data type category. It has no default and must consist of only one character. It helps us to deal with data having special character.

Example is as below:-

Let’s have data as below:-

Now if we are using regular characters only with LIKE then result will be as below:-

Now if we are using regular characters with wildcard character “%” then:-

Now if we are using regular characters with wildcard character “_” then:-

Now if we are using regular characters with wildcard character “[]” then:-

Now if we are using regular characters with wildcard character “^” then:-

Now come to the ESCAPE clause which facilitates us to handle special character in our data. Now let’s assume, if we want all the records which have data exact to “A%B” then how will we handle?
To get the better understanding first have the look at below query and the result returned by that:-

Now to handle the special character so that we can get the result set having the exact value (“A%B”), have a look at below query and its result:-