Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / SQL

SQL LIKE Operator

Rate me:
Please Sign up or sign in to vote.
4.80/5 (29 votes)
9 Mar 2009CPOL2 min read 105K   45   12
This article discusses some unknown feature of SQL LIKE operator e.g. using set, range, exclusion and escape sequences

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.

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

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

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

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

SQL
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

  • 9th March, 2009: Initial post

License

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


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

Comments and Discussions

 
BugDouble negative ... Pin
boesman6-Jun-14 4:44
boesman6-Jun-14 4:44 
GeneralRe: Double negative ... Pin
rolan_19-Jan-15 3:19
rolan_19-Jan-15 3:19 
GeneralRe: Double negative ... Pin
boesman9-May-15 5:25
boesman9-May-15 5:25 
GeneralRe: Double negative ... Pin
rolan_2-Jun-15 11:48
rolan_2-Jun-15 11:48 
GeneralVote of 5 Pin
CyberAngel6-Nov-12 2:56
CyberAngel6-Nov-12 2:56 
GeneralMy vote of 5 Pin
guayasen_o14-Feb-11 1:29
guayasen_o14-Feb-11 1:29 
GeneralGood........ Pin
Vivek Johari8-Dec-10 5:28
Vivek Johari8-Dec-10 5:28 
GeneralLIKE extension Pin
db_developer27-Jan-10 9:35
db_developer27-Jan-10 9:35 
Questionstandard sql? Pin
Berndele19-Mar-09 9:40
Berndele19-Mar-09 9:40 
GeneralVery Nice Pin
Iftikhar Akram9-Mar-09 22:21
Iftikhar Akram9-Mar-09 22:21 
GeneralKudos! Pin
bvermilion9-Mar-09 7:36
bvermilion9-Mar-09 7:36 
GeneralGood Pin
Esat Pllana9-Mar-09 7:18
Esat Pllana9-Mar-09 7: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.