Match open-ended pattern

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