SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples
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
LIKEoperator. - Ensure you test how your SQL performs when using the
LIKEoperator.
Related Information:
- Oracle SUBSTR Function – SQL Syntax Examples
The Oracle SUBSTR SQL Function...
- Oracle INSTR Function – SQL Syntax Examples
The Oracle INSTR SQL function...
- Oracle TO_DATE Function – SQL Syntax Examples
The Oracle TO_DATE SQL function...
- Oracle SUBSTR with INTSR Function – SQL Syntax Examples
Oracle SUBSTR and INSTR SQL...
- Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
This article provides common examples...
- First and Last Day of Month – Oracle SQL Syntax Examples
First Day of Current Month...
- XMLFOREST vs XMLELEMENT – Missing vs Empty in SQLX
Like me, some people may...
- Oracle APEX Tutorial 2 – Parameterized Report Part 2 – Video Training
This Tutorial is a guided...

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%’