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
