SQL number functions - use to manipulate retrieved numbers

Define, syntax and an example with output for

ABS(number)

Returns the absolute positive value of an expression.
Syntax:
ABS(expression)
Example:
SELECT ABS(-1.0), ABS(0.0), ABS(1.0)
Output:
1.0   .0   1.0

CEIL(number)

Returns the smallest integer greater than, or equal to, the specified numeric expression.
Syntax:
CEILING(expression)
Example:
SELECT CEILING($223.45), CEILING($-223.45), CEILING($0.0)
Output:
224.00   -223.00   0.00

FLOOR(number)

Returns the largest integer less than, or equal to, the specified numeric expression.
Syntax:
FLOOR(expression)
Example:
SELECT FLOOR($223.45), CEILING($-223.45), CEILING($0.0)
Output:
223.00   -224.00   0.00

MOD(number, divisor)

Returns the remainder of the division from 2 integer values.
Syntax:
MOD(dividend, divisor)
Example:
SELECT MOD(20,3)
Output:
2

POWER(number, power)

Returns the exponential value for the numeric expression.
Syntax:
POWER(number, power)
Example:
SELECT POWER(2.0, 3.0)
Output:
8.0

SIGN(number)

Returns the sign i.e. positive or negative value for the numeric expression. It returns -1 for negative expressions, a value of 0 for zero
Syntax:
SIGN(number)
Example:
SELECT SIGN(4)
Output:
1

ROUND(number, precision)

Returns the numeric value rounded off to the next value specified.
Syntax:
ROUND(number, number of places)
Example:
SELECT ROUND(1.3456, 2)
SQRT(number)

Returns the square root value of the expression.
Syntax:
SQRT(number)
Example:
SELECT SQRT(4.0)
Output:
2.0

TRUNC(number, precision)

Returns a numeric value that truncate to the specific places
Syntax:
TRUNCATE(number,places)
Example:
SELECT TRUNCATE(1.3456, 2)
Output:
1.34
SQL single-row character munipulation functions
SQL single-row character munipulation functions - CONCAT(string1, string2), INITCAP(string), LENGTH(string), LPAD(string, #,padding_char), RPAD(string, #,padding_char), LTRIM(string,searchString), RTRIM(string,searchString)...
SQL single-row date functions
SQL single-row date functions - SYSDATE, ADD_MONTHS(Date, months_to_add), LAST_DAY(Date), MONTHS_BETWEEN(Date1, Date2)...
Subqueries
Subqueries - Explain subqueries with an example, A multiple-row subquery with an example, Single-row subqueries with an example...
Post your comment

You may like