Logical Operators - Pattern Matching

The use of the LIKE predicate:

The comparison operators discussed so far have compared one value, exactly to one other value. Such a precision may not always be desired or necessary. For this purpose Oracle provides a predicate LIKE.

The LIKE depreciate allows for a comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters. Two wildcard characters that are available are:

For character data types:

1. The Percent sign (%) matches any string.

2. The Underscore (_) matches any single character.

Example:

1.
Retrieve all information about suppliers whose names begin with the letters 'ja' from supplier_master.

SELECT * FROM supplier_master WHERE supplier_name LIKE 'ja %':

2.
Retrieve all information about suppliers where the second character of names are either 'r' or 'h'.

SELECT * FROM supplier_master WHERE supplier_name LIKE '_R%' OR supplier_name LIKE '_h%';

The IN and NOT IN predicates:

The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values the the IN predicate is used. One can check a single value against multiple values by using the IN predicate.

Example:

Retrieve the supplier_name,address1,address2,city and pincode from the table supplier_master where the supplier_name is either Murali or Mahesh or yatish or leela.

SELECT supplier_name,address1,address2,city,pincode FROM supplier_master WHERE supplier_name IN('murali','mahes','yatish','leela');

The NOT IN predicate is the opposite of the IN predicate. This will select all the rows where values do not match all of the values in the list.

Logical Operators - Pattern Matching