Oracle NOT LIKE Operator

In Oracle, NOT LIKE operator allows you to combine the NOT operator with the LIKE operator to perform pattern that does not match search specific pattern in a column. This allows you to perform pattern matching.

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:

NOT LIKE Operator	Description
WHERE Address NOT LIKE 'c%'	Find field values that does not starts with "c"
WHERE Address NOT LIKE '%c'	Find field values does not ends with "c"
WHERE Address NOT LIKE '%and%'	Find field values that does not have "and" in any position
WHERE Address NOT LIKE '_2%'	Find field values that does not have "2" in the second position
WHERE Address NOT LIKE 'c%z'	Find field values that does not starts with "a" and ends with "z"

Oracle NOT LIKE OPERATOR Syntax

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

Syntax

SELECT col1, col2, ...
FROM tablename
WHERE colN NOT 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 NOT 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 NOT LIKE '12-13%';

Note:

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

Oracle NOT 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 NOT LIKE '%Bhopal';

Note:

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

Oracle NOT 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 NOT LIKE '_2%';

Note:

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

« Previous