Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.

1 % - Represents zero, one, or multiple characters

2 _ - Represents a single character (MS Access uses a question mark (?) instead)

Example

Employee Table

emp_id emp_name salary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E3 James 55000 D2 M2
E4 Michael 25000 D2 M2
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3

The following SQL statement selects all employees with a employee name starting with “a”

SELECT *  FROM Employee WHERE emp_name LIKE 'a%';

Output

emp_id emp_name salary dept_id manager_id
E5 Ali 20000 D10 M3

The following SQL statement selects all employees with a employee name ending with “s”

SELECT * FROM Employee WHERE emp_name LIKE '%s';

Output

emp_id emp_name salary dept_id manager_id
E3 James 55000 D2 M2

The following SQL statement selects all employees with a employee name that have “a” in any position

SELECT * FROM Employee WHERE emp_name LIKE '%a%';

Output

emp_id emp_name salary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E3 James 55000 D2 M2
E4 Michael 25000 D2 M2
E5 Ali 20000 D10 M3

The following SQL statement selects all employees with a employee name that starts with “a” and are at least 3 characters in length:

SELECT *  FROM Employee WHERE emp_name LIKE 'a__%';

Output

emp_id emp_name salary dept_id manager_id
E5 Ali 20000 D10 M3