SQL – Like Operator
Table Of Contents:
- What Is SQL Like Operator?
- Syntax Of SQL Like Operators.
- Examples Of Like Operators.
(1) What Is SQL Like Operator?
- The
LIKE
operator is used in aWHERE
clause to search for a specified pattern in a column. - There are two wildcards often used in conjunction with the
LIKE
operator: - The percent sign (%) represents zero, one, or multiple characters
- The underscore sign (_) represents one, single character
Note:
- MS Access uses an asterisk (*) instead of the percentage sign (%), and a question mark (?) instead of the underscore (_).
(2) Syntax Of SQL Like Operator.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Some Examples:
(3) Examples Of SQL Like Operator.
Demo Data:
Example-1: CustomerName Starts From ‘a’
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
Example-2: CustomerName Ends With ‘a’
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
Example-3: CustomerName Have ‘or’ In Any Position
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%'
Example-4: CustomerName Have ‘r’ In Second Position
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
Example-5: CustomerName that starts with “a” and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
Example-6:ContactName that starts with “a” and ends with “o”
SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';
Example-7:CustomerName that does NOT start with “a”:
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';