Numeric Functions

Numeric functions return numeric values (integer, long, float, double, biginteger, bigdecimal). They generally take numeric values as inputs, though some take strings.

Standard Numeric Operators (+ - * /)

These operators take x in {integer, long, float, double, biginteger, bigdecimal}, and their return type is the same as x.

The precision and scale of non-bigdecimal arithmetic functions results match that of Java. The results of bigdecimal operations match Java, except for division, which uses a preferred scale of max(16, dividend.scale + divisor.precision + 1), which then has trailing zeros removed by setting the scale to max(dividend.scale, normalized scale).

Other Numeric Functions

Function	Definition	Data Type Constraint
`ABS(x)`	Absolute value of x	See standard numeric operators above
`ACOS(x)`	Arc cosine of x	x in {double, bigdecimal}, return type is double
`ASIN(x)`	Arc sine of x	x in {double, bigdecimal}, return type is double
`ATAN(x)`	Arc tangent of x	x in {double, bigdecimal}, return type is double
`ATAN2(x,y)`	Arc tangent of x and y	x, y in {double, bigdecimal}, return type is double
`CEILING(x)`	Ceiling of x	x in {double, float}, return type is double
`COS(x)`	Cosine of x	x in {double, bigdecimal}, return type is double
`COT(x)`	Cotangent of x	x in {double, bigdecimal}, return type is double
`DEGREES(x)`	Convert x radians to degrees	x in {double, bigdecimal}, return type is double
`EXP(x)`	e^x	x in {double, float}, return type is double
`FLOOR(x)`	Floor of x	x in {double, float}, return type is double
`FORMATBIGDECIMAL(x, y)`	Format x using format y	x is bigdecimal, y is string, returns string
`FORMATBIGINTEGER(x, y)`	Format x using format y	x is biginteger, y is string, returns string
`FORMATDOUBLE(x, y)`	Format x using format y	x is double, y is string, returns string
`FORMATFLOAT(x, y)`	Format x using format y	x is float, y is string, returns string
`FORMATINTEGER(x, y)`	Format x using format y	x is integer, y is string, returns string
`FORMATLONG(x, y)`	Format x using format y	x is long, y is string, returns string
`LOG(x)`	Natural log of x (base e)	x in {double, float}, return type is double
`LOG10(x)`	Log of x (base 10)	x in {double, float}, return type is double
`MOD(x, y)`	Modulus (remainder of x / y)	x in {integer, long, float, double, biginteger, bigdecimal}, return type is same as x
`PARSEBIGDECIMAL(x, y)`	Parse x using format y	x, y are strings, returns bigdecimal
`PARSEBIGINTEGER(x, y)`	Parse x using format y	x, y are strings, returns biginteger
`PARSEDOUBLE(x, y)`	Parse x using format y	x, y are strings, returns double
`PARSEFLOAT(x, y)`	Parse x using format y	x, y are strings, returns float
`PARSEINTEGER(x, y)`	Parse x using format y	x, y are strings, returns integer
`PARSELONG(x, y)`	Parse x using format y	x, y are strings, returns long
`PI()`	Value of Pi	return is double
`POWER(x,y)`	x to the y power	x in {double, bigdecimal, biginteger}, return is the same type as x
`RADIANS(x)`	Convert x degrees to radians	x in {double, bigdecimal}, return type is double
`RAND()`	Return random number in the range 0.0 <= x < 1.0	return is double; while rand() is pushed down to MySQL, PostgreSQL and Oracle, it's not pushed down to Microsoft SQL Server.
`RAND_SEED(x)`	Return random number using new generator seeded with x	x is integer, returns double
`ROUND(x, y)`	Round x to y places; negative values of y indicate places to the left of the decimal point	x in {integer, float, double, bigdecimal} y is integer, return is same type as x
`SIGN(x)`	1 if x > 0, 0 if x = 0, -1 if x < 0	x in {integer, long, float, double, biginteger, bigdecimal}, return type is integer
`SIN(x)`	Sine value of x	x in {double, bigdecimal}, return type is double
`SQRT(x)`	Square root of x	x in {long, double, bigdecimal}, return type is double
`TAN(x)`	Tangent of x	x in {double, bigdecimal}, return type is double
`BITAND(x, y)`	Bitwise AND of x and y	x, y in {integer}, return type is integer
`BITOR(x, y)`	Bitwise OR of x and y	x, y in {integer}, return type is integer
`BITXOR(x, y)`	Bitwise XOR of x and y	x, y in {integer}, return type is integer
`BITNOT(x)`	Bitwise NOT of x	x in {integer}, return type is integer

Parsing Numeric Data Types from Strings

The Data Virtuality Server offers a set of functions you can use to parse numbers from strings. For each string, you need to provide the formatting of the string. These functions use the convention established by the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats from the Oracle Java documentation.

For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to parse strings and return the data type you need:

Input String	Function Call to Format String	Output Value	Output Data Type
`'$25.30'`	`PARSEDOUBLE(cost, '$#,##0.00;($#,##0.00)')`	`25.3`	`double`
`'25%'`	`PARSEFLOAT(percent, '#,##0%')`	`25`	`float`
`'2,534.1'`	`PARSEFLOAT(total, '#,##0.###;-#,##0.###')`	`2534.1`	`float`
`'1.234E3'`	`PARSELONG(amt, '0.###E0')`	`1234`	`long`
`'1,234,567'`	`PARSEINTEGER(total, '#,##0;-#,##0')`	`1234567`	`integer`

The correct format of input string depends on the server locale. The pattern must follow conventions of U.S. English notation.

Formatting Numeric Data Types as Strings

The Data Virtuality Server offers a set of functions you can use to convert numeric data types into strings. For each string, you need to provide the formatting. These functions use the convention established within the java.text.DecimalFormat class to define the formats you can use with these functions. You can learn more about how this class defines numeric string formats from the Oracle Java documentation.

For example, you could use these function calls, with the formatting string that adheres to the java.text.DecimalFormat convention, to format the numeric data types into strings:

Input Value	Input Data Type	Function Call to Format String	Output String
`25.3`	`double`	`FORMATDOUBLE(cost, '$#,##0.00;($#,##0.00)')`	`'$25.30'`
`25`	`float`	`FORMATFLOAT(percent, '#,##0%')`	`'25%'`
`2534.1`	`float`	`FORMATFLOAT(total, '#,##0.###;-#,##0.###')`	`'2,534.1'`
`1234`	`long`	`FORMATLONG(amt, '0.###E0')`	`'1.234E3'`
`1234567`	`integer`	`FORMATINTEGER(total, '#,##0;-#,##0')`	`'1,234,567'`