SQL Like Operator (Article on CodeProject.com)

My article about some unknown features of SQL LIKE operator is published on codeproject.com. This is the same article as published in my blog.
SQL Like Operator

Some useful features of 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 search a pattern in a text. When it comes to SQL we find ourselves struggling to write filter expression. There are some features of SQL LIKE operator which might help you writing better filter expressions.

Discussion
There are following features which I would discuss 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 lying 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 has '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 start 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.