Database Reference

In-Depth Information

Speaking of rounding, let's see what the actual ROUND function will do. ROUND is used

to round a value to the precision specified. If you specify a negative number for the preci-

sion, it counts off that value left from the decimal point. If you specify a positive number, it

counts off right from the decimal point. A value of zero places the precision at the decimal

point. If a negative number is specified that is greater than the number for digits left of the

decimal, 0 is returned. The following query demonstrates several results from specifying

different precisions.

SELECT ROUND(13,18,1) FROM dual;

Output: 13.2

SIGN

If you just want to find out if a value is positive or negative, you can use the SIGN function.

If the value is zero, it returns 0. If the value is positive, it returns 1. If the value is negative,

it returns -1.

SELECT SIGN(5.31), SIGN(0), SIGN(-5.31) FROM dual;

Output: 1 0 - 1

SQUARE and SQRT

You can square a value using SQUARE or find the square root using SQRT.

These are straightforward, so we'll just show you an example.

SELECT SQUARE(3) FROM dual;

Output: 9

Or

SELECT SQRT (14) FROM dual;

Output: 4

String Functions

UPPER and LOWER

Two of the things we want to do the most with string data is change the characters to either

all uppercase characters or all lowe rcase characters. UPPER is used for the former and

LOWER for the latter. As with all expressions, these can be used in any part of the state-

ment that supports expressions.

SELECT UPPER(Staff_name)

FROM Staff;

LTRIM and RTRIM