LIKE comparisons in SQL Server

LIKE operator is typically used for wild-card pattern matching.  However, all pattern matching with a LIKE operator does not always return in a SARGable (Searchable Argument) expression which means that an index may or may not be used when using a LIKE operator.

col1 LIKE ‘abc%’

will make use of an index since the wild card search is done after specifying the initial characters, however

col1 like ‘%abc%’

will not make use of a seek operation on the index.

Majority of the wildcard character searches are clearly defined in BOL - i.e. using %, _ ,[], [^] (you can look up BOL for “LIKE comparisons” and all this is detailed)…let’s cover an example that details pattern matching using the escape clause.  If the string that you want to do pattern matching on, also includes one or more of the special wildcard characters, then you need to make use of the escape clause - example:

declare @table table (col1 varchar(10))
insert into @table values (’John Doe’)
insert into @table values (’John%’)
insert into @table values (’John%Doe’)

select * from @table where col1 like ‘John%’

col1
———-
John Doe
John%
John%Doe

However, if you want to match only the entries that have the wildcard % in them, then you can use the escape clause - example:

declare @table table (col1 varchar(10))
insert into @table values (’John Doe’)
insert into @table values (’John%’)
insert into @table values (’John%Doe’)

select * from @table where col1 like ‘John!%%’ escape ‘!’

col1
———-
John%
John%Doe

The escape character could be any character as long as that is not the character that you want to include in the pattern matching…example, this will return the same results:

select * from @table where col1 like ‘John+%%’ escape ‘+’