Database Reference
In-Depth Information
Figure 9.14
Start at the
Character
Following the
Blank Space to Get
the Last Name.
In the next extract, when the minutes are NULL, the word “Unknown”
is substituted for the null value using the NVL function. Additionally, the
words “Minutes and ” are concatenated onto the minutes.
NVL(SUBSTR(PLAYING_TIME,1,INSTR(PLAYING_TIME,':')-1)
,'Unknown')||' Minutes and '
Next, seconds are extracted using SUBSTR and INSTR again. Then,
add the NVL function to substitute the word “Unknown” if the results are
NULL. In this case, there are trailing blanks, because the column is CHAR
datatype (rather than VARCHAR2, which excludes trailing blanks). So, add
the RTRIM function to remove the trailing blanks.
RTRIM(NVL(SUBSTR(PLAYING_TIME,INSTR(PLAYING_TIME,':')+1)
,'Unknown'))||' Seconds.'
Finally, concatenate the minutes to the seconds and additionally concat-
enate “ Seconds.” to the end of the whole thing. The first two columns are
displayed as reference points so you can see how the functions have worked
