| 23. 6. 1. LIKE |
|
|
- 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>
|
|