SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples

Posted on Jun 13, 2009 in Technical Tips | 8 comments

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...

2. Oracle INSTR Function – SQL Syntax Examples

   The Oracle INSTR SQL function...

3. Oracle TO_DATE Function – SQL Syntax Examples

   The Oracle TO_DATE SQL function...

4. Oracle SUBSTR with INTSR Function – SQL Syntax Examples

   Oracle SUBSTR and INSTR SQL...

5. Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)

   This article provides common examples...

6. First and Last Day of Month – Oracle SQL Syntax Examples

   First Day of Current Month...

7. XMLFOREST vs XMLELEMENT – Missing vs Empty in SQLX

   Like me, some people may...

8. Oracle APEX Tutorial 2 – Parameterized Report Part 2 – Video Training

   This Tutorial is a guided...