LIKE : Like « PL SQL Operators « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Operators
3. Like

1. LIKE is PL/SQL's pattern-matching operator.
2. LIKE is used to compare a character string against a pattern.
3. LIKE is useful for performing wildcard searches.
4. LIKE can only be used with character strings.
5. LIKE checks to see if the contents of string_variable match the pattern definition.
6. If the string matches the pattern, a result of true is returned;
7. otherwise, the expression evaluates to false.

The Syntax for LIKE

string_variable LIKE pattern

where

string_variable represents any character string variable, whether VARCHAR2, CHAR, LONG, and so on.

pattern represents a pattern.

pattern can also be a string variable, or it can be a string literal.

Two wildcard characters are defined for use with LIKE, the percent sign (%) and the underscore (_).

'%' matches any number of characters in a string.

'/' matches exactly one.

For example, the pattern 'New %' will match 'New Car', 'New Bycle', 'New Horse'.

The pattern '___day' is looking for a six-letter word ending with the letters 'day'.

That would match 'Monday', 'Friday', and 'Sunday'.

A function using the LIKE operator to return a phone number's area code.

SQL>
SQL> CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)
  2  RETURN VARCHAR2 AS
  3  BEGIN
  4    IF phone_number LIKE '___-___-____' THEN
  5      --we have a phone number with an area code.
  6      RETURN SUBSTR(phone_number,1,3);
  7    ELSE
  8      --there is no area code
  9      RETURN 'none';
 10     END IF;
 11  END;
 12  /

Function created.

SQL>