SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples

By M&S Consulting (Ashok) on Jun 13th, 2009
Filed Under Technical Tips // Tags:

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.
Bookmark and Share

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

2 Responses to “SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples”

  1. abid
    Nov 04, 2009
    Reply

    i want to enter any data in a MSflexgrid at a time i want search and see in this MSFlexgrid. is it possible? if it possible, how can i do it?


    • ashok.aggarwal
      Mar 02, 2010
      Reply

      Your specific question is not very clear, however, in the context of implementing a LIKE operation within a UI control like MSFlexGrid, your back-end data access could automatically append a percent (%) sign at the end and/or beginning of the string the user entered into the UI.



Leave a Reply


Archives

Recent Comments

  • SaintClair said: Thanks a million for this priceless material. May God richly bless you!
  • ashok.aggarwal said: Thank you for your comment, Stuart. I’m glad this helped you.
  • Stuart said: I’d really like to thank you for your post. It was really that simple.
  • ashok.aggarwal said: Your specific question is not very clear, however, in the context of implementing a LIKE...
  • Jonas said: Finally in print that a user is required to show the email option. Thanks.

Calendar

March 2010
M T W T F S S
« Feb    
1234567
891011121314
15161718192021
22232425262728
293031  

Featured Testimonial

"M&S has taken 'partnership' to a new level. They are not only a deeply skilled service provider, the M&S team is actually a mission critical component to our operations, taking on new initiatives as well as maintaining custom, open source, and COTS solutions."

CEO, MedPivot

Free SQL Service