Database Reference
In-Depth Information
numerous. Some of them are CLIENT_INFO, ENTRYID, ISDBA,
LANG, LANGUAGE, SESSIONID, and TERMINAL. The SES-
SIONID is likely to be different for every session.
USERENV('SESSIONID') = 1520
VSIZE(expression)
. The number of bytes in an expression.
VSIZE('Oracle Certified Professional') = 29
VSIZE(100.234) = 5
Note:
Regular expression functions are covered in Chapter 14. XML func-
tions are covered in Chapter 17. BFILENAME is covered in Chapter 16.
Let's take a quick peek at the DECODE function. The next query uses
the home state of the artist in a whimsical DECODE statement, replacing
the name of the state with various phrases. For example, the state code
“OR” is replaced by the phrase “Tree hugger.” The default value is always
listed last after the search-and-replace value pairs. In this example, the
default is the phrase “Whatever!” Figure 9.12 shows the result.
SELECT STATE_PROVINCE
, DECODE(STATE_PROVINCE,'CA','Surfer',
'NH','Snow bunny',
'OR', 'Tree hugger',
'FL', 'Retired',
'Whatever!')
FROM ARTIST;
Now let's look at combining various functions.
9.3
Combining Functions
So far in this chapter, we have looked at a lot of single-row function defini-
tions. Now let's get to more practical uses for these functions by combining
various functions into single expressions. Remember, single-row functions
return a single value when passed various parameters and always work with
the values on one row of data at a time.
Let's use a combination of SUBSTR and INSTR to retrieve the first
name of the artist. We know that there will always be a space between the
first name and the last name. So we use the INSTR function to determine
the location of the space and then use SUBSTR to return the characters
