SQL LIKE

This is a tutorial for the SQL LIKE operator, with examples.

The LIKE operator is used to test character data to see if it matches a given pattern.

Sample Data

The examples in this article need some sample data. The following script creates and populates the test table:

-- Sample data create table test(s nvarchar(10)) insert test values ('abcde') insert test values ('abcee') insert test values ('abcfe') insert test values ('abcge') insert test values ('hijklmn')

Matching Multiple Characters

The simplest, and most common, use is with the % wildcard character. This matches zero, one or more characters.

The most efficient way, which will use an index if one exists, is to precede the % with as many characters as are known:

select * from test where s like 'abc%' This returns:


Any number of wildcards can be included:

select * from test where s like '%f%' This returns:

Matching a Single Character

The underscore character (_) represents one, and exactly one, character:

select * from test where s like 'ab_d_' This returns

Matching a Selection or Range of Characters

A list of characters within square brackets means that the character at that position must be one of those listed. For example:

select * from test where s like 'abc[def]e' returns:


You may also specify a range using the hyphen (-):

select * from test where s like 'abc[e-g]e' returns:


And the ^ character means NOT one of the characters specified:

select * from test where s like 'abc[^def]e' returns:

Escape Characters

An escape character allows you to tell the query parser to treat a wildcard as a normal character, and not interpret it as a wildcard. First add a new row:

insert test values ('ab% off') Suppose you want rows starting with the characters "ab%". This doesn't work:

select * from test where s like 'ab%%' Because it matches all these rows:


The trick is to specify that the first % is to be treated as the character "%". This is done by placing another character just before it. In my example I will use "*" as the escape character, but any character not in the string will do. Try the following:

select * from test where s like 'ab*%%' escape '*'

Summary

This article has described, with the aid of examples, how to use the SQL LIKE operator.

For a further explanation of how it can affect performance, I have covered its use in SQL WHERE Clause Optimization.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.