SQL LIKE operator Match open-ended pattern
Example
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 |
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 |
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 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 |
