Read an Excerpt
From Chapter 5: Getting Text Information and Changing It
...In a restaurant, for instance, one of the Prep cook's job "functions" is to turn orange juice, vegetables into salad, and roasts into slices of meat. This function takes an object (orange, vegetable, roast) and changes it (into juice, salad, slices). The "result" of the function is a modified version of the original object.The buyer for the restaurant counts the oranges, the vegetables, and the roasts. This job function does not change the objects, but instead describes them. It still produces a "result" in the form of a number. It tells you "how many."
Functions in ORACLE also work in these two ways. Some functions produce a result that is a modification of the original information, such as turning lowercase characters into uppercase in a sentence. Other functions produce a result that tells you something about the information, such as how many characters there are in a word or sentence. The two types of functions are really no more conceptually difficult than this.
Note
If you are using PL/SQL, you can create your own functions for use within PL/SQL blocks with the create function statements. See Chapter 22 for details.
Data Types
Just as people can be classified into different types, based on certain characteristics (shy, ornery, outgoing, smart, silly), different kinds of data can be classified into what are called data "types" based on certain characteristics.
Data types in ORACLE include NUMBER, CHAR (short for CHARACTER), DATE, VARCHAR2, LONG, RAW, and LONG RAW. The first three are probably obvious. The last four are special data types that you'llencounter later. A full explanation of each of these can be found by name or under "Data Types" in the Alphabetical Reference section. Each will be covered in detail in the chapters ahead. As with people, some of the "types" overlap and some are fairly rare.
If the information is the character (VARCHAR2 or CHAR) type of information—a mixture of letters, punctuation marks, numbers, and spaces (also called alphanumeric)—you'll need string functions to modify or inform you about it. ORACLE's SQL provides quite a few such tools.
What Is a String?
A string is a simple concept: a bunch of things in a line, like a string of houses, popcorn or pearls, numbers, or a string of characters in a sentence or a soap opera.
Strings are frequently encountered in managing information. Names are strings of characters, as in "Juan L'Heureaux." Phone numbers are strings of numbers, dashes, and sometimes parentheses, as in "(415) 555-2676." Even a pure number, such as "5443702," can be considered as either a number or a string of characters.
Note
Data types that are restricted to pure numbers (plus a decimal point and minus sign, if needed) are called "NUMBER," and are not usually referred to as strings. A number can be used in certain ways that a string cannot, and vice versa.
Strings that can include any mixture of letters, numbers, spaces, and other symbols (such as those above the numbers on your keyboard) are called character strings, or just character for short.
In ORACLE, character is abbreviated "CHAR". This is pronounced "care," like the first part of the word "character." It is not pronounced "CHAR" as in "CHARcoal," or "car." The latter two pronunciations are inappropriate because they take the user a step further away from the actual meaning (the abbreviation is bad enough) and can confuse nonprogrammers.
There are two string data types in ORACLE7. In older version of ORACLE, the CHAR data type was a varying length string. In ORACLE7, CHAR strings are always a fixed length. If you set a value to a string with a length less than that of a CHAR column, ORACLE automatically pads the string with blanks. When you compare CHAR strings, ORACLE compares the strings by padding them out to equal lengths with blanks. This means that is you compare "character" with "character" in CHAR columns, ORACLE considers the strings to be the same. The VARCHAR2 data type is a varying-length string with the same behavior as CHAR in previous versions of ORACLE. The VARCHAR data type is synonymous with VARCHAR2, but this may change in future versions of ORACLE, so you should avoid using VARCHAR. Use CHAR for fixed-length character string fields and VARCHAR2 for all other character string fields.
The simple ORACLE string functions, explained in this chapter, are shown in Table 5-1. You will learn more advanced string functions and how to use them in Chapter 14...