Numeric Functions | Ignite Documentation
Edit

# Numeric Functions

## ABS

``ABS (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Returns the absolute value of an expression.

### Example

Calculate an absolute value:

``SELECT transfer_id, ABS (price) from Transfers;``

## ACOS

``ACOS (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the arc cosine. This method returns a `double`.

### Example

Get arc cos value:

``SELECT acos(angle) FROM Triangles;``

## ASIN

``ASIN (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the arc sine. This method returns a `double`.

### Example

Calculate an arc sine:

``SELECT asin(angle) FROM Triangles;``

## ATAN

``ATAN (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the arc tangent. This method returns a `double`.

### Example

Get an arc tangent:

``SELECT atan(angle) FROM Triangles;``

## COS

``COS (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the trigonometric cosine. This method returns a `double`.

### Example

Get a cosine:

``SELECT COS(angle) FROM Triangles;``

## COSH

``COSH (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the hyperbolic cosine. This method returns a `double`.

### Example

Get an hyperbolic cosine:

``SELECT HCOS(angle) FROM Triangles;``

## COT

``COT (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the trigonometric cotangent (1/TAN(ANGLE)). This method returns a `double`.

### Example

Gets a​ trigonometric cotangent:

``SELECT COT(angle) FROM Triangles;``

## SIN

``SIN (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the trigonometric sine. This method returns a `double`.

### Example

Get a trigonometric sine:

``SELECT SIN(angle) FROM Triangles;``

## SINH

``SINH (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the hyperbolic sine. This method returns a `double`.

### Example

Get a hyperbolic sine:

``SELECT SINH(angle) FROM Triangles;``

## TAN

``TAN (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the trigonometric tangent. This method returns a `double`.

### Example

Get a trigonometric tangent:

``SELECT TAN(angle) FROM Triangles;``

## TANH

``TANH (expression)``

### Parameters

• `expression` - may be a column name, a result of another function, or a math operation.

### Description

Calculates the hyperbolic tangent. This method returns a `double`.

### Example

Get a hyperbolic tangent:

``SELECT TANH(angle) FROM Triangles;``

## ATAN2

``ATAN2 (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

Calculates the angle when converting the rectangular coordinates to polar coordinates. This method returns a `double`.

### Example

Get a hyperbolic tangent:

``SELECT ATAN2(X, Y) FROM Triangles;``

## BITAND

``BITAND (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

The bitwise AND operation. This method returns a `long`.

### Example

``SELECT BITAND(X, Y) FROM Triangles;``

## BITGET

``BITGET (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

Returns true if and only if the first parameter has a bit set in the position specified by the second parameter. This method returns a `boolean`. The second parameter is zero-indexed; the least significant bit has position 0.

### Example

Check that 3rd bit is 1:

``SELECT BITGET(X, 3) from Triangles;``

## BITOR

``BITOR (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

The bitwise OR operation. This method returns a `long`.

### Example

Calculate OR between two fields:

``SELECT BITGET(X, Y) from Triangles;``

## BITXOR

``BITXOR (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

The bitwise XOR operation. This method returns a `long`.

### Example

Calculate XOR between two fields:

``SELECT BITXOR(X, Y) FROM Triangles;``

## MOD

``MOD (y, x)``

### Parameters

• `x and y` - the arguments.

### Description

The modulo operation. This method returns a `long`.

### Example

Calculate MOD between two fields:

``SELECT BITXOR(X, Y) FROM Triangles;``

## CEILING

``````CEIL (expression)
CEILING (expression)``````

### Parameters

• `expression` - any valid numeric expression.

### Description

See also Java Math.ceil. This method returns a `double`.

### Example

Calculate a ceiling price for items:

``SELECT item_id, CEILING(price) FROM Items;``

## DEGREES

``DEGREES (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.toDegrees`. This method returns a `double`.

### Example

Converts the argument value to degrees:

``SELECT DEGREES(X) FROM Triangles;``

## EXP

``EXP (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.exp`. This method returns a `double`.

### Example

Calculates exp:

``SELECT EXP(X) FROM Triangles;``

## FLOOR

``FLOOR (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.floor`. This method returns a `double`.

### Example

Calculates floor price:

``SELECT FLOOR(X) FROM Items;``

## LOG

``````LOG (expression)
LN (expression)``````

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.log`. This method returns a `double`.

### Example

Calculates LOG:

``SELECT LOG(X) from Items;``

## LOG10

``LOG10 (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.log10` (in Java 5). This method returns a `double`.

### Example

Calculate LOG10:

``SELECT LOG(X) FROM Items;``

``RADIANS (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Example

``SELECT RADIANS(X) FROM Items;``

## SQRT

``SQRT (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.sqrt`. This method returns a `double`.

### Example

Calculates SQRT:

``SELECT SQRT(X) FROM Items;``

## PI

``PI (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.PI`. This method returns a `double`.

### Example

Calculates PI:

``SELECT PI(X) FROM Items;``

## POWER

``POWER (X, Y)``

### Parameters

• `expression` - any valid numeric expression.

### Description

See also `Java Math.pow`. This method returns a `double`.

### Example

Calculate the ​power of 2:

``SELECT pow(2, n) FROM Rows;``

## RAND

``{RAND | RANDOM} ([expression])``

### Parameters

• `expression` - any valid numeric expression seeds the session’s random number generator.

### Description

Calling the function without a parameter returns the next a pseudo random number. Calling it with a parameter seeds the session’s random number generator. This method returns a `double` between 0 (including) and 1 (excluding).

### Example

Gets a random number for every play:

``SELECT random() FROM Play;``

## RANDOM_UUID

``{RANDOM_UUID | UUID} ()``

### Description

Returns a new UUID with 122 pseudo random bits.

### Example

Gets random number for every Player:

``SELECT UUID(),name FROM Player;``

## ROUND

``ROUND ( expression [, precision] )``

### Parameters

• `expression` - any valid numeric expression.

• `precision` - the number of digits after the decimal to round to. Rounds to the nearest long if the number of digits if not set.

### Description

Rounds to a number of digits, or to the nearest long if the number of digits if not set. This method returns a `numeric` (the same type as the input).

### Example

Convert every Player’s age to an integer number:

``SELECT name, ROUND(age) FROM Player;``

## ROUNDMAGIC

``ROUNDMAGIC (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

This function is good for rounding numbers, but it can be slow. It has special handling for numbers around 0. Only numbers smaller than or equal to `+/-1000000000000` are supported. The value is converted to a String internally, and then the last 4 characters are checked. '000x' becomes '0000' and '999x' becomes '999999', which is rounded automatically. This method returns a `double`.

### Example

Round every Player’s age:

``SELECT name, ROUNDMAGIC(AGE/3*3) FROM Player;``

## SECURE_RAND

``SECURE_RAND (int)``

### Parameters

• `int` - specifies the number​ of digits.

### Description

Generate a number of cryptographically secure random numbers. This method returns `bytes`.

### Example

Get a truly random number:

``SELECT name, SECURE_RAND(10) FROM Player;``

## SIGN

``SIGN (expression)``

### Parameters

• `expression` - any valid numeric expression.

### Description

Return -1 if the value is smaller 0, 0 if zero, and otherwise 1.

### Example

Get a sign for every value:

``SELECT name, SIGN(VALUE) FROM Player;``

## ENCRYPT

``ENCRYPT (algorithmString , keyBytes , dataBytes)``

### Parameters

• `algorithmString` - sets a supported AES algorithm.

• `keyBytes` - sets a key.

• `dataBytes` - sets data.

### Description

Encrypt data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns `bytes`.

### Example

Encrypt players name:

``SELECT ENCRYPT('AES', '00', STRINGTOUTF8(Name)) FROM Player;``

## DECRYPT

``DECRYPT (algorithmString , keyBytes , dataBytes)``

### Parameters

• `algorithmString` - sets a supported AES algorithm.

• `keyBytes` - sets a key.

• `dataBytes` - sets data.

### Description

Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.

### Example

Decrypt Players' names:

``SELECT DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) FROM Player;``

## TRUNCATE

``{TRUNC | TRUNCATE} (\{\{numeric, digitsInt} | timestamp | date | timestampString})``

### Description

Truncates to a number of digits (to the next value closer to 0). This method returns a `double`. When used with a timestamp, truncates a timestamp to a date (day) value. When used with a date, truncates a date to a date (day) value less time part. When used with a timestamp as string, truncates a timestamp to a date (day) value.

### Example

``TRUNCATE(VALUE, 2);``

## COMPRESS

``COMPRESS(dataBytes [, algorithmString])``

### Parameters

• `dataBytes` - data to compress.

• `algorithmString` - an algorithm to use for compression.

### Description

Compress the data using the specified compression algorithm. Supported algorithms are: LZF (faster but lower compression; default), and DEFLATE (higher compression). Compression does not always reduce size. Very small objects and objects with little redundancy may get larger. This method returns `bytes`.

### Example

``COMPRESS(STRINGTOUTF8('Test'))``

## EXPAND

``EXPAND(dataBytes)``

### Parameters

• `dataBytes` - data to expand.

### Description

Expand data that was compressed using the COMPRESS function. This method returns `bytes`.

### Example

``UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))``

## ZERO

``ZERO()``

### Description

Return the value 0. This function can be used even if numeric literals are disabled.

### Example

``ZERO()``