as CHAR strings might yield different results than the same values compared as VARCHAR2 strings. These issues are discussed later in this chapter in the section "Use of Comparison Operators with Strings."

LIKE
LIKE is PL/SQL's pattern-matching operator and is used to compare a character string against a pattern. It's especially useful for performing wildcard searches when you need to retrieve data from the database and you aren't exactly sure of the spelling of your search criteria. Unlike the other comparison operators, LIKE can only be used with character strings.

The Syntax for LIKE

string_variable LIKE pattern

In this syntax, string_variable represents any character string variable, whether VARCHAR2, CHAR, LONG, and so on. pattern represents a pattern. This can also be a string variable, or it can be a string literal.

The LIKE operator 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.

NEW TERM
Two wildcard characters are defined for use with LIKE, the percent sign (%) and the underscore (_).The percent sign matches any number of characters in a string, and the underscore matches exactly one. For example, the pattern `New %' will match `New York', `New Jersey', `New Buffalo', and any other string beginning with the word `New `. Another example is the pattern `___day'. It is looking for a six-letter word ending with the letters `day', and would match `Monday', `Friday', and `Sunday'. It would not match `Tuesday', `Wednesday', `Thursday', or `Saturday' because those names have more than three letters preceding `day'.

Listing 4.3 shows a short function that makes use of the LIKE operator to return the area code from a phone number. Figure 4.1 shows how the function works.

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

 1: 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: /

OUTPUT

Function created.
SQL>

ANALYSIS
The preceding code simply creates a stored function. Type it into SQL*Plus exactly as it is shown. The LIKE operator is used in line 4 to see if the phone number matches the standard XXX-XXX-XXXX format, which would indicate that an area code is part of the number. Figure 4.1 demonstrates the use of this area_code function.

Figure 4.1.
The area_code
function in action.

BETWEEN
The BETWEEN operator tests to see if a value falls within a given range of values.

The Syntax for BETWEEN

the_value [NOT] BETWEEN low_end AND high_end

In this syntax, the_value is the value you are testing, low_end represents the low end of the range, and high_end represents the high end of the range.

A result of true is returned if the value in question is greater than or equal to the low end of the range and less than or equal to the high end of the range.

You might have already guessed that the BETWEEN operator is somewhat redundant. You could easily replace any expression using BETWEEN with one that used <= and >=. The equivalent expression would look like this:

(the_value >= low_end) AND (the_value <= high_end)

Table 4.5 shows some expressions using BETWEEN and the equivalent expressions using <= and >=.

Table 4.5. Expressions using the BETWEEN operator.

IN
The IN operator checks to see if a value is contained in a specified list of values. A true result is returned if the value is contained in the list; otherwise, the expression evaluates to false.

The Syntax for IN

the_value [NOT] IN (value1, value2, value3,...)

In this syntax, the_value is the value you are testing, and value1, value2, value3,... represents a list of comma-delimited values.

A result of true is returned if the value in question matches one of the values in the list.

Table 4.6 shows some examples of the IN operator in use.

Table 4.6. Expressions using the IN operator.

Listing 4.4 shows a short sample of code that uses the IN operator to see if a holiday will result in a three-day weekend.

INPUT
Listing 4.4. The IN operator used to test for long weekends.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   test_date     DATE;
 4:   day_of_week   VARCHAR2(3);
 5:   years_ahead   INTEGER;
 6: BEGIN
 7:   --Assign a date value to test_date.
 8:   --Let's use Independence Day.
 9:   test_date := TO_DATE(`4-Jul-1997','dd-mon-yyyy');
10:   --Now let's look ahead ten years and see how many
11:   --three day July 4 weekends we can expect.
12:   FOR years_ahead IN 1..10 LOOP
13:     --get the name for the day of the week.
14:     day_of_week := TO_CHAR(test_date,'Dy');
15:     --most employers give an extra day if July 4 falls on a weekend.
16:     IF day_of_week IN (`Mon','Fri','Sat','Sun') THEN
17:       DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,'dd-Mon-yyyy')
18:                            || `     A long weekend!');
19:     ELSE
20:       DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,'dd-Mon-yyyy')
21:                              || ` Not a long weekend.');
22:     END IF;
23:     --advance one year (12 months)
24:     test_date := ADD_MONTHS(test_date,12);
25:   END LOOP; --for each year
26: END;
27: /

OUTPUT

04-Jul-1997     A long weekend!
04-Jul-1998     A long weekend!
04-Jul-1999     A long weekend!
04-Jul-2000 Not a long weekend.
04-Jul-2001 Not a long weekend.
04-Jul-2002 Not a long weekend.
04-Jul-2003     A long weekend!
04-Jul-2004     A long weekend!
04-Jul-2005     A long weekend!
04-Jul-2006 Not a long weekend.
PL/SQL procedure successfully completed.

ANALYSIS
The preceding code checks the date for Independence Day over a 10-year period, and tells you whether or not it will result in a long weekend. Line 9 is where the starting date of 4 July 1997 is set. A FOR loop is used in lines 12 through 25 to check the July 4 day for a 10-year period. In line 14, the TO_CHAR function is used to retrieve the day of the week on which July 4 falls during the year in question. Line 16 uses the IN operator to test for a long weekend. The obvious cases to check for are when Independence Day falls on a Monday or a Friday, resulting in a three-day weekend. The test in line 16 also includes Saturday and Sunday because many employers still give employees a day off, resulting in a three-day weekend. Line 24 uses PL/SQL's ADD_MONTHS function to advance the date twelve months, which is one year. You will read more about ADD_MONTHS on Day 7, "Using Oracle's Built-in Functions." It is also covered in Appendix B, "Oracle Functions Reference."