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.

Returns the absolute value of the number X.

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

Returns the smallest integer value not less than X.

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

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

Returns the largest integer value not greater than X.

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

[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:

• Normalize()

Returns the length in chars of the string str.

SELECT LENGTH('text');
=> 4

Returns the natural logarithm of X.

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

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

Returns the base-2 logarithm of X.

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

Returns the base-10 logarithm of X.

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

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

New for v 5.0

Returns the “murmur” hash of the string X.

SELECT MURMURHASH('test');
=> 1026673864

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. Pay attention, that return value type is always double.

SELECT ROUND(-1.23);		SELECT ROUND(-1.23,1);	
=> -1.000000		        => -1.200000
 
SELECT ROUND(-1.58);		SELECT ROUND(-1.586,2);
=> -2.000000			=> -1.590000
 
SELECT ROUND(1.58);		SELECT ROUND(1.589,1);
=> 2.000000			=>1.600000

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

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

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

Returns the non-negative square root of X.

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

Returns the square of X.

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

Returns the number X, truncated to D decimals. If D is 0, the result will have no fractional part. If D is negative, the integer part of the number is zeroed out. Pay attention, that return value type is always double.

SELECT TRUNCATE(1.223,1);
=> 1.200000
SELECT TRUNCATE(1.999,1);
=> 1.900000
SELECT TRUNCATE(1.999,0);
=> 1.000000
SELECT TRUNCATE(-1.999,1);
=> -1.900000

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));

Sleeps (pauses) for the number of seconds given by the X argument. Where the argument X is positive integer . Returns 1 if function was successfully completed, otherwise 0.

X - number of seconds

This function has the following restrictions: 1) The maximum sleep time is 10 minutes. If the received number is more than 10 minutes, it is reduced to 10 minutes. 2) Only admin can use it if he is in under server.

SELECT sleep(10)