Mega Code Archive
LIKE
<![CDATA[
LIKE is PL/SQL's pattern-matching operator.
LIKE is used to compare a character string against a pattern.
LIKE is useful for performing wildcard searches.
LIKE can only be used with character strings.
LIKE checks to see if the contents of string_variable match the pattern definition.
If the string matches the pattern, a result of true is returned;
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>
]]>