SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples

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

12 Comments on “SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples”

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

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

  2. How will retrieve record from MS Access database by throwing query from Visual Basic 6 by using ‘Like’ Operator

  3. Can anybody help me how to make a search/find query? I’ve been trying to do it but it seems like it doesn’t work.
    What I put in my code is this…
    AdoSearch.RecordSource = “Select * from TABLE where FIELD Like ‘” & Text1.Text & “*'”

    this is only working if I type exactly what I input in the text box. What I want to happen is I want to search data in a specific field and gives me output that is same/close to it.

    help anyone

  4. select employee name start with ‘a’ without using “Like” operator.

    please give a query for this problemmmm.

  5. How can I use LIKE operator to check multiple expressions?? for eg. If i want to select all employees whose last names start with Y and Z.

    LOGICAL req:
    Select * from EMPLOYEE where EMPLOYEE.LAST_NAME like (‘Y%’,’Z%’)

    Appreciate your responses.
    Thanks

    1. Hi,
      You can use it like this I suppose

      select * from emp where emp.last_name like ‘Y%’ or emp.last_name like ‘Z%’;

  6. list the name ; name should not start with letter ‘A’ and should not end with letter ‘A’; but ‘A’ should be there in the name other than first and last character …

Leave a Reply to MarkD Cancel reply

Your email address will not be published. Required fields are marked *