Single-Row Functions - Oracle SQL Jumpstart with Examples - page 182

SUBSTR(expression, [-]position[, length]) . The SUBSTR func-

tion returns a portion of a string. If the length parameter is omitted,
then all characters after the value of position are returned. If the posi-
tion parameter is positive, then the substring value is extracted from
the left of the string; otherwise, if the parameter is negative, the value
is extracted from the right (end) of the string.

SUBSTR('oracle certified professional', 8,9)
= 'certified'
SUBSTR('oracle certified professional',-12,12)
= 'professional'

Here is a quick example using some of the string functions men-
tioned previously. Figure 9.6 shows the results. The query shows the
complete value of the NAME column, followed by the length of the
value, a section of the name, and finally, the position of the second
occurrence of the letter “a” in the name. Notice that the INSTR func-
tion returns zero if it cannot find a match.

SELECT NAME, LENGTH(NAME) "Length"
, SUBSTR(NAME,5,5) "Letters 5 thru 9"
, INSTR(NAME,'a',1,2) "Second a"
FROM ARTIST;
Now let's proceed to number functions.

9.2.2 Number Functions

Number functions require numbers, not strings, as input. They nearly
always return a numeric value.

ABS(n) . Finds an absolute value of a number. An absolute value
function returns the positive or unsigned value of a negative or posi-
tive number.

ABS(-125) = 125
ABS(125) = 125
CEIL(n) and FLOOR(n) . Ceiling and floor are similar to rounding
and truncating functions. Ceiling returns the next integer greater
than n . Floor returns the next integer less than n .

CEIL(1.1) = 2