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 (_).
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 (_).
|'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".|
Consider a database tables called Employee with the following records:
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';
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
In My Access, the same can be achieved using below query:
/* My Access SQL code */ SELECT * FROM Employee WHERE Salary LIKE '3*0';