Click here to Skip to main content
13,836,160 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


45 bookmarked
Posted 9 Mar 2009
Licenced CPOL

SQL LIKE Operator

Rate this:
Please Sign up or sign in to vote.
This article discusses some unknown feature of SQL LIKE operator e.g. using set, range, exclusion and escape sequences


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.


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. 


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.


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.


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'.


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': 


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.


  • 9th March, 2009: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Muhammad Shujaat Siddiqi
Software Developer (Senior)
United States United States
Muhammad Shujaat Siddiqi
New Jersey, USA

You may also be interested in...

Comments and Discussions

BugDouble negative ... Pin
boesman6-Jun-14 5:44
memberboesman6-Jun-14 5:44 
GeneralRe: Double negative ... Pin
rolan_19-Jan-15 4:19
memberrolan_19-Jan-15 4:19 
GeneralRe: Double negative ... Pin
boesman9-May-15 6:25
memberboesman9-May-15 6:25 
GeneralRe: Double negative ... Pin
rolan_2-Jun-15 12:48
memberrolan_2-Jun-15 12:48 
GeneralVote of 5 Pin
CyberAngel6-Nov-12 3:56
memberCyberAngel6-Nov-12 3:56 
GeneralMy vote of 5 Pin
guayasen_o14-Feb-11 2:29
memberguayasen_o14-Feb-11 2:29 
GeneralGood........ Pin
Vivek Johari8-Dec-10 6:28
memberVivek Johari8-Dec-10 6:28 
GeneralLIKE extension Pin
db_developer27-Jan-10 10:35
memberdb_developer27-Jan-10 10:35 
Questionstandard sql? Pin
Berndele19-Mar-09 10:40
memberBerndele19-Mar-09 10:40 
GeneralVery Nice Pin
Iftikhar Akram9-Mar-09 23:21
memberIftikhar Akram9-Mar-09 23:21 
GeneralKudos! Pin
bvermilion9-Mar-09 8:36
memberbvermilion9-Mar-09 8:36 
GeneralGood Pin
Esat Pllana9-Mar-09 8:18
memberEsat Pllana9-Mar-09 8:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190114.1 | Last Updated 9 Mar 2009
Article Copyright 2009 by Muhammad Shujaat Siddiqi
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid