Suppose we have to search all the employees whose name starts with ‘Ka’. How do we query this? SQL provides an operator LIKE, where we can compare only part of the column values.
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMPLOYEE WHERE EMP_NAME LIKE ‘Ka%’;
In the above query, ‘%’ is used to indicate that it can be anything and any number of characters. It will display all the employees whose name starts with ‘Ka’
We can search the characters in any combination by using LIKE and %.
If we need to restrict the number of characters to one at the end of ‘Ka’, then we can use ‘-‘ instead of ‘%’.
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMPLOYEE WHERE EMP_NAME LIKE ‘Ka_’;
Above query will return all the employees with 3 letter names starting with ‘Ka’.
Similar to ‘%’, ‘_’ can also be used in any combination in the query.
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMPLOYEE WHERE EMP_NAME LIKE ‘[KMR] %’; -- this will return the employees whose name starts with K, M or R
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMPLOYEE WHERE EMP_NAME LIKE ‘[K-N] %’; -- this will return the employees whose name starts with K, L, M, and N
SELECT EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_SSN FROM EMPLOYEE WHERE EMP_NAME LIKE ‘[! KMR] %’; -- this will return the employees whose name DOES NOT start with K, M or R