SQL LIKE Operator

Introduction 

This article discusses some unknown feature of SQL LIKE operator e.g. using set, range, exclusion and escape sequences in where expression with SQL LIKE operator.

Background  

While working in any .NET and other programming languages, we are used to using regular expression for searching a pattern in a text. When it comes to SQL, we find ourselves struggling to write filter expressions. There are some features of SQL LIKE operator which might help you in writing better filter expressions. 

Discussion

I will discuss the following features in this article:

1. Range of values
2. List of values
3. Exclusion of a list or range of values
4. Escape Sequence 

Range Of Values

Like in regular expression, if we want to search based on a sequence, we use bracket to specify the sequence. e.g. [1-5] means all digits between 1 and 5 inclusive or [a-z] means all characters between 'a' and 'z'. Do you know that the same expressions can be used in SQL Server like operator.

SELECT * FROM TBL_MYTABLE WHERE MYCOL LIKE '[a-r]%' 

In the above examples, we have selected all rows for which MYCOL starts with any character between 'a' and 'r'.

List Of Values 

Again like in Regular expression, if we want to search for a list of values, we can search using square brackets. In this example, we want to select all those rows which have 'a','e','i','o' or 'u' as data in MYCOL column. The query is very easy.

SELECT * FROM TBL_MYTABLE WHERE MYCOL LIKE '[aeiou]'

Exclusion Of a List or Range of Values

This is used when we want to exclude any set of character or range of character. In this example, we are excluding those rows for which MYCOL does not start with any character in the range of characters from 'a' to 'r'.

SELECT * FROM TBL_MYTABLE WHERE MYCOL LIKE '[^a-r]%'

We can also exclude a list of characters. Like in the example below, we are excluding all rows where value of MYCOL starts with any of 'a','e','i','o' or 'u': 

SELECT * FROM TBL_MYTABLE WHERE MYCOL LIKE '[^aeiou]%'

Escape Sequence

What if % is part of your data you want to filter based on some filter criteria in SQL through LIKE operator. The obvious answer is use escape sequence so that we could specify to the runtime that we are expecting % as data. But do you know how we specify escape sequence in SQL Like operator.

You can do that using escape operator after the filter criteria. Let us see an example in which '%' and '_' can be part of our data. We want to select only those rows which contain these characters as data.

SELECT * FROM TBL_MyTable where MyCol LIKE '%[\%\_]%' {ESCAPE '\'}

It must be remembered that using more than one character as escape sequence results in Error.

History

• 9^th March, 2009: Initial post