Sql LIKE operator

Match open-ended pattern
The % wildcard appended to the beginning or end (or both) of a string will allow 0 or more of any character before the beginning or after the end of the pattern to match.
Using '%' in the middle will allow 0 or more characters between the two parts of the pattern to match.
We are going to use this Employees Table:
Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
1 John Johnson 2468101214 1 1 400 23-03-2005
2 Sophie Amudsen 2479100211 1 1 400 11-01-2010
3 Ronny Smith 2462544026 2 1 600 06-08-2015
4 Jon Sanchez 2454124602 1 1 400 23-03-2005
5 Hilde Knag 2468021911 2 1 800 01-01-2000
Following statement matches for all records having FName containing string 'on' from Employees Table.
SELECT * FROM Employees WHERE FName LIKE '%on%';
Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
3 Ronny Smith 2462544026 2 1 600 06-08-2015
4 Jon Sanchez 2454124602 1 1 400 23-03-2005
The following statement matches all records having PhoneNumber starting with string '246' from Employees.
SELECT * FROM Employees WHERE PhoneNumber LIKE '246%';
Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
1 John Johnson 2468101214 1 1 400 23-03-2005
3 Ronny Smith 2462544026 2 1 600 06-08-2015
5 Hilde Knag 2468021911 2 1 800 01-01-2000
The following statement matches all records having PhoneNumber ending with string '11' from Employees.
SELECT * FROM Employees WHERE PhoneNumber LIKE '%11'
Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
2 Sophie Amudsen 2479100211 1 1 400 11-01-2010
5 Hilde Knag 2468021911 2 1 800 01-01-2000
All records where Fname 3rd character is 'n' from Employees.
SELECT * FROM Employees WHERE FName LIKE '__n%';
(two underscores are used before 'n' to skip the first 2 characters)
Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date
3 Ronny Smith 2462544026 2 1 600 06-08-2015
4 Jon Sanchez 2454124602 1 1 400 23-03-2005
Single character match
To broaden the selections of a structured query language (SQL-SELECT) statement, wildcard characters, the percent sign (%), and the underscore (_), can be used.
The _ (underscore) character can be used as a wildcard for any single character in a pattern match.
Find all employees whose Fname starts with 'j' and ends with 'n' and has exactly 3 characters in Fname.
SELECT * FROM Employees WHERE FName LIKE 'j_n'
_ (underscore) character can also be used more than once as a wild card to match patterns.
For example, this pattern would match "jon", "jan", "jen", etc.
These names will not be shown "jn","john","jordan", "justin", "jason", "julian", "jillian", "joann" because in our query one underscore is used and it can skip exactly one character, so result must be of 3 character Fname.
For example, this pattern would match "LaSt", "LoSt", "HaLt", etc.
SELECT * FROM Employees WHERE FName LIKE '_A_T'
ESCAPE statement in the LIKE-query
If you implement a text-search as LIKE-query, you usually do it like this:
SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%')
However, (apart from the fact that you shouldn't necessarily use LIKE when you can use full-text-search) this creates a problem when somebody inputs text like "50%" or "a_b".
So (instead of switching to full text-search), you can solve that problem using the LIKE-escape statement:
SELECT *
FROM T_Whatever
WHERE SomeField LIKE CONCAT('%', @in_SearchText, '%') ESCAPE ''
That means will now be treated as ESCAPE character. This means, you can now just prepend to every character in the string you search, and the results will start to be correct, even when the user enters a special character like % or _.
e.g.
string stringToSearch = "abc_def 50%";
string newString = "";
foreach(char c in stringToSearch)
newString += @"" + c;
sqlCmd.Parameters.Add("@in_SearchText", newString);
// instead of sqlCmd.Parameters.Add("@in_SearchText", stringToSearch);
Note: The above algorithm is for demonstration purposes only. It will not work in cases where 1 graphene consists out of several characters (utf-8). e.g. string stringToSearch = "Les Miseu0301rables"; You'll need to do this for each grapheme, not for each character. You should not use the above algorithm if you're dealing with Asian/East-Asian/South-Asian languages. Or rather, if you want the correct code to begin with, you should just do that for each grapheme cluster.
Search for a range of characters
The following statement matches all records having FName that starts with a letter from A to F from Employees Table.
SELECT * FROM Employees WHERE FName LIKE '[A-F]%'
Match by range or set
Match any single character within the specified range (e.g.: [a-f]) or set (e.g.: [abcdef]).
This range pattern would match "gary" but not "mary":
SELECT * FROM Employees WHERE FName LIKE '[a-g]ary'
This set pattern would match "mary" but not "gary":
SELECT * FROM Employees WHERE Fname LIKE '[lmnop]ary'
The range or set can also be negated by appending the ^ caret before the range or set:
This range pattern would not match "gary" but will match "mary":
SELECT * FROM Employees WHERE FName LIKE '[^a-g]ary'
This set pattern would not match "mary" but will match"gary":
SELECT * FROM Employees WHERE Fname LIKE '[^lmnop]ary'
Wildcard characters
wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table.
Wildcards in SQL are:%, _, [charlist], [^charlist]
% - A substitute for zero or more characters
Eg: //selects all customers with a City starting with "Lo"
SELECT * FROM Customers
WHERE City LIKE 'Lo%';
//selects all customers with a City containing the pattern "es"
SELECT * FROM Customers
WHERE City LIKE '%es%';
_ - A substitute for a single character
Eg://selects all customers with a City starting with any character, followed by "erlin"
SELECT * FROM Customers
WHERE City LIKE '_erlin';
[charlist] - Sets and ranges of characters to match
Eg://selects all customers with a City starting with "a", "d", or "l"
SELECT * FROM Customers
WHERE City LIKE '[adl]%';
//selects all customers with a City starting with "a", "d", or "l"
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
[^charlist] - Matches only a character NOT specified within the brackets
Eg://selects all customers with a City starting with a character that is not "a", "p", or "l"
SELECT * FROM Customers
WHERE City LIKE '[^apl]%';
or
SELECT * FROM Customers
WHERE City NOT LIKE '[apl]%' and city like '_%';
ATutorialHub Related Guide
Comments (9)
User Comments

panduranga gupta
2021-07-05 07:03:13good website for learning and help me a lot

raju
2021-09-25 14:58:47The awsome website i am looking like for a long time, good work atutorialhub team keep doing

Shivani
2021-09-01 15:03:56Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

Harshitha
2021-09-10 15:05:45It is very helpful to students and easy to learn the concepts

Sowmya
2021-09-14 15:06:41Great job Tutorials are easy to understand Please make use of it

Zain Khan
2021-09-18 15:07:23Great content and customized courses.

Rudrakshi Bhatt
2021-09-09 15:08:10Well structured coursed and explained really well!

Pavana Somashekar
2021-09-11 15:09:08Good platform for beginners and learn a lot on this website

Sax
2021-09-25 19:35:50Nice website
Leave a Comment