SQL - LIKE Clause
The SQL LIKE clause is used in a SQL WHERE clause to search for a specified pattern in a specified column. There are two wildcards which are often used in conjunction with the LIKE operator.
- % : represents zero, one or multiple characters.
- _ : represents one character.
Note: In MS Access, asterisk (*) is used instead of percentage sign (%) and a question mark (?) is used instead of (_).
Syntax
The syntax for using LIKE Clause is given below:
SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;
The below table describes patterns which is used with LIKE operator and uses (%) and (_).
Pattern | Description |
---|---|
'J%' | A value that start with "J". |
'%n' | A value that end with "n". |
'%oh%' | A value that have "oh" in any position. |
'_o%' | A value that have "o" in the second position. |
'J_%' | A value that start with "J" and have at least 2 characters. |
'J%n' | A value that start with "J" and ends with "n". |
Example:
Consider a database tables called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To fetch all records from Employee table where Salary starts with 3 and ends with 0, the SQL query is:
SELECT * FROM Employee WHERE Salary LIKE '3%0'; /* My Access SQL code */ SELECT * FROM Employee WHERE Salary LIKE '3*0';
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000