Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle LIKE Operator

In Oracle, LIKE operator allows you to perform pattern matching search in a column. The Oracle LIKE operator can be used with SELECT, INSERT, UPDATE and DELETE statement with the combination of WHERE clause.

The percent (%) sign represents zero, one, or multiple characters and underscore (_) represents a single character.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE OperatorDescription
WHERE Address LIKE 'c%'Finds field values starts with "c"
WHERE Address LIKE '%c'Finds field values ends with "c"
WHERE Address LIKE '%and%'Finds field values that have "and" in any position
WHERE Address LIKE '_2%'Finds field values that have "2" in the second position
WHERE Address LIKE 'c%z'Finds Finds values that starts with "a" and ends with "z"

Oracle LIKE OPERATOR Syntax

To search a record based on pattern matching use LIKE operator, use the following syntax:

Syntax

SELECT col1, col2, ...
FROM tablename
WHERE colN LIKE pattern;  

Parameters:

tablename: The table name from which you want to fetch the records.

pattern: To perform pattern matching search in a column.

Oracle LIKE OPERATOR example using % (percent) wildcard

The following Oracle statement is used to search a value based on pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '12-13%';

Note:

In the above example, we are showing how to use the LIKE operator to search a "address" starting with '12-13'.

Oracle LIKE OPERATOR example using % (percent) wildcard

The following Oracle statement is used to search a based on pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '%Bhopal';

Note:

In the above example, we are showing how to use the LIKE operator to search a "address" ending with 'Bhopal'.

Oracle LIKE OPERATOR example using _ (underscore) wildcard

The following Oracle statement is used to search a record based on single character:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan 
WHERE address LIKE '_2%';

Note:

In the above example, we are showing how to use the LIKE operator using underscore (_) to search a address that have "2" in the second position with SELECT statement.