Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL LIKE Operator

, 9 Mar 2009
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

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

  • 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)

Share

About the Author

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

Comments and Discussions

 
BugDouble negative ... Pinmemberboesman6-Jun-14 4:44 
GeneralVote of 5 PinmemberCyberAngel6-Nov-12 2:56 
GeneralMy vote of 5 Pinmemberguayasen_o14-Feb-11 1:29 
GeneralGood........ PinmemberVivek Johari8-Dec-10 5:28 
This article is really very good and helpful..........
GeneralLIKE extension PinmemberRoman_K27-Jan-10 9:35 
Questionstandard sql? PinmemberBerndele19-Mar-09 9:40 
GeneralVery Nice PinmemberIFFI9-Mar-09 22:21 
GeneralKudos! Pinmemberbvermilion9-Mar-09 7:36 
GeneralGood PinmemberEsat Pllana9-Mar-09 7:18 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 9 Mar 2009
Article Copyright 2009 by Muhammad Shujaat Siddiqi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid