Filtering Character Data - Wildcards used in LIKE patterns

Wildcard                                    Meaning

.................................................

%                                               Any string including an empty one

.................................................

_(underscore)                             A single character

.................................................

[<character list>]                      A single character from a list

.................................................

[<character range>]                 A single character from a range

.................................................

[^<character list or range>]        A single character that is not in the list or range

Note : 

1. ! is escape character.
2. col LIKE '%ABC%'
   SQL Server cannot rely on index ordering anymore

reference: reference:Training Kit Query SQL Server 2012,CHAPTER 3,page 69

Filtering Character Data - Filtering Unicode type

It's typical bad habit to specify a regular character string literal when the filtered column is of a Unicode type Example:
              SELECT empid, firstname, lastname
              FROM HR.Employees
              WHERE lastname = 'Davis'
Why:
The column and the literal have types, SQL Server implicitly converts one operand's type to the other.

But:
In this example,SQL Server converts the literal's type to the column's type, so it can still efficiently rely on indexing.

The Proper form:
              SELECT empid, firstname, lastname
              FROM HR.Employees
              WHERE lastname = N'Davis'


reference:Training Kit Query SQL Server 2012,CHAPTER 3,page 68