The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text.

LIKE provides a utility to conduct simple text pattern matching without the full power regular expressions, and hence, is simpler than using regular expressions.

Simple Matching with % and _

• % - matches any number of character(s), including the absence of characters
• _ - matches a single character

Oracle LIKE Examples

No Special Matching Specified

In this case, LIKE does essentially the same job as the equals (=) operator matching all employee last_names exactly with the string “King”:

1
2
3
4
5
6
7

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'King';
 
--returns:
--Janette King
--Steven King

Using %

In this case, any employee last names that start with “Green…” will be matched.

1
2
3
4
5
6
7

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'Green%';
 
--returns:
--Nancy Greenberg
--Danielle Greene

Note: If there was someone in our table with a last_name of “Green” (and no letters after “n”, they would also be returned). You can see this exemplified below:

1
2
3
4
5
6
7

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'King%';
 
--returns:
--Janette King
--Steven King

Of course, you can use % anywhere in your matching string. Following is an example when % is used at the start of the string:

1
2
3
4
5
6
7
8

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE '%ing%';
 
--returns:
--Payam Kaufling
--Janette King
--Steven King

Using _

In this case, any employee last names that start with “Green…” AND have exactly one character more in the last name will be matched.

1
2
3
4
5
6

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'Green_';
 
--returns:
--Danielle Greene

Going back to our “King” example:

1
2
3
4
5

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'King_';
 
--returns: [no rows]

Potential Mistakes

The default matching behavior is that of an equals (=). Oracle SQL does not assume that you intended for % to be included at the end of your string. Also, you cannot use a star (*) to conduct matching. Star is treated the same as any other character:

1
2
3
4
5
6
7
8
9
10
11

SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'Kin';
 
--returns: [no rows]
 
SELECT first_name || ' ' || last_name 
FROM hr.employees e 
WHERE last_name LIKE 'Kin*';
 
--returns: [no rows]

Notes

• Oracle does provide regular expression matching in a separate operator called REGEXP_LIKE.
• Microsoft SQL Server provides regular expression matching within their LIKE operator.
• Ensure you test how your SQL performs when using the LIKE operator.

Related Information:

1. Oracle SUBSTR Function - SQL Syntax Examples The Oracle SUBSTR SQL Function is very common, and many times misused. Below shows the SUBSTR function along with the...
2. Oracle INSTR Function - SQL Syntax Examples The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...
3. Oracle TO_DATE Function - SQL Syntax Examples The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...
4. Oracle SUBSTR with INTSR Function - SQL Syntax Examples Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...
5. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE) This article provides common examples of TO_CHAR using dates....
6. Oracle APEX Tutorial 2 - Parameterized Report Part 1 - Video Training This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....
7. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...
8. Oracle APEX Tutorial 2 - Parameterized Report Part 2 - Video Training This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....