Last updated on September 13th, 2022 at 03:38 pm
The like operator is used with the where clause for searching some specified pattern from a column. If you are unfamiliar with SQL datatype, check out my SQL Datatypes tutorial on this blog.
SQL wildcard characters are used for substituting one or more characters in a string. Wildcard operators, or you can say characters, are used with LIKE operators.
The Types of SQL wildcard operators are the following.
- %
- _
- [char list]
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.
Let’s get in brief with all the characters one by one.
% operator in SQL
The % operator in SQL is used to find any string from a column whose complete string information is unknown to you.
Let’s understand this with an example.
Table_1: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Example 1
Select * from employee where city like 'P%';Output
Explanation
This query resulted in all the details of an employee whose city name starts with P.
Example 2
Select * from employee where city like '%a';Output
Explanation
The above query resulted in all the details of an employee whose city name ends with a.
Example 3
select * from employee where city like '%p%';Output
Explanation
The above query resulted in all the details of an employee whose city name has p in any position.
An _ operator in SQL
The _ operator is used as a substitute for one or more characters based on the number of times _ is used. Let’s understand this with an example.
See the following table.
Table_1: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Example 1
Select * from employee where city like '_atna';Output
Explanation
The above SQL statements select all employees whose city starts with any character followed by “atna”.
Example 2
select * from employee where city like 'Kol_a_a';Output:
Explanation:
The following SQL statement selects all types of employees whose city starts with Kol followed by any character, then followed by a character, then ends with a.
Operations performed by both % and _ operator
Table_1: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Example 1
Select * from employee where state like '_u%';Output
Explanation
The above query will print the details of an employee whose state name 1st position starts with any character followed by u and rest any other characters.
Example 2
select * from employee where state like 'a_%_%';Output
Explanation
The above query will print the details of an employee whose state name 1st position starts with A, followed by any other characters but should be of at least three characters in length.
[char list] operator
The char list operator represents any single character within the brackets.
Suppose, if we declare h[ao]t in a query, the resulting set will consist of either hat as output or hot as output but not hit as i is not present in the brackets.
Let’s clear this up with more examples.
Consider the following table.
Table_1: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Example 1
select * from employee where city like '[PJA]%';Output
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
Explanation
The above query will print the details of the employee whose city the first character either starts with ‘P’ or ‘J’ or ‘A’.
Example 2
select * from employee where city like '[!PJA]%';OR
Select * from employee where city like ‘[^PJA]%’;Output
Emp_id | Emp_name | City | State | Salary |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Explanation
The above query will print the details of the employee whose city the first character does not start with ‘P’ or ‘J’ or ‘A’. Here this query was used for fetching non-matching set or range of characters specified inside the brackets.
Example 3
Select * from employee where city like ‘[P-Z]%’;Output
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Explanation
In this query, details of all employees have displayed whose city the first character lies in the range from P to Z. Note: P and Z are inclusive.
SQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Wildcard Characters in MS Access
* | Represents zero or more characters | bl* finds bl, black, blue, and blob. |
? | Represents a single character | h?t finds a hot, hat, and hit. |
[] | Represents any single character within the brackets | h[oa]t finds hot and hat, but not hit. |
! | Represents any character not in the brackets | h[!oa]t finds hit, but not hot and hat. |
– | Represents a range of characters | c[a-b]t finds cat and cbt. |
# | Represents any single numeric character | 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295. |
Wildcard Characters in SQL Server
% | Represents zero or more characters. | bl% finds bl, black, blue, and blob. |
_ | Represents a single character. | h_t finds a hot, hat and hit. |
[] | Represents any single character within the brackets. | h[oa]t finds hot and hat, but not hit. |
^ | Represents any character, not in the brackets. | h[^oa]t finds hit, but not hot and hat. |
– | It represents a range of characters. | c[a-b]t finds cat and cbt. |
All the wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
WHERE CustomerName LIKE ‘a%’ | Finds any values that start with “a”. |
WHERE CustomerName LIKE ‘%a’ | Finds any values that end with “a”. |
WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position. |
WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position. |
WHERE CustomerName LIKE ‘a_%_%’ | Finds any values that start with “a” and are at least 3 characters long. |
WHERE ContactName LIKE ‘a%o’ | Finds values that start with “a” and end with “o”. |
That’s it for this tutorial.