Switch to: V9V8V7V6V5

Numeric Functions

We name function to be numeric if it returns a numeric value. Even if function get string parameter(s), but returns a numeric value, we name it “numeric function”.

All numeric functions return NULL in case of an error.

ABS(X)

Returns the absolute value of the number X.

SELECT ABS(2);
=> 2
SELECT ABS(-32);
=> 32

CEILING(X)

CEIL(X)

Returns the smallest integer value not less than X.

SELECT CEILING(1.23);
=> 2
SELECT CEILING(-1.23);
=> -1

EXP(X)

Returns the value e (the base of natural algorithm), raised to the power of X.

SELECT EXP(2);
=> 7.389056
SELECT EXP(-2);
=> 0.135335

FLOOR(X)

Returns the largest integer value not greater than X.

SELECT FLOOR(1.23);
=> 1
SELECT FLOOR(-1.23);
=> -2

IsNormalized( Str )

[NEW in 4.9.1]

Returns TRUE if the given string is a normalized unicode string.

SELECT * FROM T WHERE IsNormalized(fld) = false;

See also:

LENGTH( str )

Returns the length in chars of the string str.

SELECT LENGTH('text');
=> 4

LN(X)

Returns the natural logarithm of X.

SELECT LN(2);
=> 0.693147
SELECT LN(-2);
=> NULL

LOG(B, X)

If called with one parameter, this function returns the natural logarithm of X.

SELECT LOG(2);
=> 0.693147
SELECT LOG(-2);
=> NULL

If called with two parameters, this function returns the logarithm of X for an arbitrary base B.

SELECT LOG(2,65536);
=> 16.000000
SELECT LOG(1,100);
=> NULL

LOG2(X)

Returns the base-2 logarithm of X.

SELECT LOG2(65536);
=> 16.000000
SELECT LOG2(-100);
=> NULL

LOG10(X)

Returns the base-10 logarithm of X.

SELECT LOG10(2);
> 0.301030
SELECT LOG10(100);
=> 2.000000
SELECT LOG10(-100);
=> NULL

MOD( N, M )

The same do: N % M

Modulo (like the % operator in C). Returns the remainder of N divided by M.

SELECT MOD(234, 10);
=> 4
SELECT 253 % 7;
=> 1
SELECT MOD(29,9);
=> 2

MURMURHASH(X)

New for v 5.0

Returns the “murmur” hash of the string X.

SELECT MURMURHASH('test');
=> 1026673864

ROUND( X [,D] )

Returns the argument X, rounded to the nearest integer. With two arguments, returns X rounded to D decimals. If D is negative, the integer part of the number is zeroed out.

SELECT ROUND(-1.23);		SELECT ROUND(-1.23,1);	
=> -1					=> -1.2
 
SELECT ROUND(-1.58);		SELECT ROUND(-1.586,2);
=> -2					=> -1.59
 
SELECT ROUND(1.58);		SELECT ROUND(1.589,1);
=> 2					=>1.6

POWER(X,Y)

Returns the value of X raised to the power of Y:

SELECT POW(2,2);
=> 4.000000
SELECT POW(2,-2);
=> 0.250000

SIGN(X)

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.

SELECT SIGN(-32);
=> -1
SELECT SIGN(0);
=> 0
SELECT SIGN(137);
=>1

SQRT(X)

Returns the non-negative square root of X.

SELECT SQRT(4);
=> 2.000000
SELECT SQRT(20);
=> 4.472136

SQUARE (X)

Returns the square of X.

SELECT SQUARE(2);
=> 4.000000
SELECT SQUARE(4.5);
=> 20.25

TRUNCATE(X,D)

TRUNC (X,D)

Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part. If D is negative, the integer part of the number is zeroed out.

SELECT TRUNCATE(1.223,1);
=> 1.2
SELECT TRUNCATE(1.999,1);
=> 1.9
SELECT TRUNCATE(1.999,0);
=> 1
SELECT TRUNCATE(-1.999,1);
=> -1.9

RAND() / RAND (N)

Returns the random value in 0-1 range.

If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

SELECT RAND();
=> 0.486405
SELECT RAND(2);
=> 0.655587
SELECT RAND(2);
=> 0.655587

To obtain a random integer R in the range a ⇐ R < b, you can use the expression

FLOOR(a + RAND() * (b – a))

For example, to obtain a random integer in the range 15 ⇐ R < 20, you could use the following statement:

SELECT FLOOR(15 + (RAND() * 5));