SQL LIKE Operator

Use the LIKE condition to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers. You can combine pattern-matching characters. You can use the ESCAPE identifier to search for the actual % and _ symbols.

% denotes zero or many characters. 
 _ denotes one character.

Examples using % wildcard:

The first example that we’ll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the employee name whose name begins with ‘S’.

SELECT ename, empno, job, sal FROM emp WHERE ename LIKE 'S%'; 

In the same string you can also using the wildcard multiple times. We illustrate it with our example.

SELECT ename, empno, job, sal FROM emp WHERE ename LIKE '%LL%'; 

The Result of this statement we see that looking for all employee name whose name contains the characters ‘LL’.

In another example we can also use the LIKE condition to find employee name whose name does not start with ‘S’. We illustrate it with our example.

SELECT ename, empno, job, sal FROM emp WHERE ename not LIKE 'S%'; 

By placing the not keyword in front of the LIKE condition, you are able to retrieve all employee name whose name does not start with ‘S’.

Examples using _ wildcard:

Now we explain how _ wildcard character works in SQL Like. One thinks we want to clear that the _ is the only one character.

SELECT * FROM emp WHERE ename like 'Sm_th'; 

This SQL statement would return all employee names whose name is 5 characters long, where the first two characters are ‘Sm’ and the last two characters is ‘th’. For example, it could return suppliers whose name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.

Here is another example:

SELECT * FROM emp WHERE empno like '790_'; 

You might find that you are looking for an account number, but you only have 4 digits.

Examples using Escape Characters:

Next, in Oracle, let’s say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example:

SELECT * FROM emp WHERE ename LIKE '!%' escape '!'; 

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM emp WHERE ename LIKE 'H%!%' escape '!'; 

This example returns all employee name whose name starts with H and ends in %. For example, it would return a value such as ‘Hello%’.

You can also use the Escape character with the _ character. For example,

SELECT * FROM emp WHERE ename LIKE 'H%!_' escape '!'; 

This example returns all employee name whose name starts with H and ends in _. For example, it would return a value such as ‘Hello_’.