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

« Previous