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”
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?
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.
How will retrieve record from MS Access database by throwing query from Visual Basic 6 by using ‘Like’ Operator
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
You want to try something like:
select * from TABLE where FIELD Like ‘%XYZ%’;
The “%” (instead of the “*”) is the wildcard in the Oracle LIKE command.
select employee name start with ‘a’ without using “Like” operator.
please give a query for this problemmmm.
Why not use LIKE? That is exactly what it is for.
SELECT * FROM Employee WHERE Name LIKE ‘a%’
Select employee_name from employee where substr(employee_name,1,1)=’a’;
how to pattern match without using like operator?
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
Hi,
You can use it like this I suppose
select * from emp where emp.last_name like ‘Y%’ or emp.last_name like ‘Z%’;
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 …