Skip to main content
Skip table of contents

Numeric Functions

You are looking at an older version of the documentation. The latest version is found here.

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 the 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 the 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'

See Also

Check if a String Can Be Cast to a Number Using isNumeric for a ready-to-use CREATE statement

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close