Offshore Software Outsourcing Company, offshore IT outsourcing services, offshore software development Russia

About ALTOROS: ALTOROS Systems is headquartered in Tampa, Florida and maintains an office near Boston, Massachusetts and technology development center in Belarus and Russia. ALTOROS specializes on providing value-added e-commerce and web-based software development and offshore software outsourcing services to emerging enterprises helping them successfully plan and implement business initiatives.

All mathematical functions return NULL in case of an error.

Unary minus. Changes the sign of the argument:

mysql> SELECT - 2;
-> -2

Note that if this operator is used with a BIGINT, the return value is a BIGINT! This means that you should avoid using - on integers that may have the value of -2^63!

ABS(X)

Returns the absolute value of X:

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

This function is safe to use with BIGINT values.

SIGN(X)

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

mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1

MOD(N,M)

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

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

This function is safe to use with BIGINT values.

FLOOR(X)

Returns the largest integer value not greater than X:

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

Note that the return value is converted to a BIGINT!

CEILING(X)

Returns the smallest integer value not less than X:

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

Note that the return value is converted to a BIGINT!

ROUND(X)

Returns the argument X, rounded to the nearest integer:

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2

Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead.

ROUND(X,D)

Returns the argument X, rounded to a number with D decimals. If D is 0, the result will have no decimal point or fractional part:

mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X:

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

LN(X)

Returns the natural logarithm of X:

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

This function was added in MySQL version 4.0.3. It is synonymous with LOG(X) in MySQL.

LOG(X)

LOG(B,X)

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

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

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

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

The arbitrary base option was added in MySQL version 4.0.3. LOG(B,X) is equivalent to LOG(X)/LOG(B).

LOG2(X)

Returns the base-2 logarithm of X:

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

LOG2() is useful for finding out how many bits a number would require for storage. This function was added in MySQL version 4.0.3. In earlier versions, you can use LOG(X)/LOG(2) instead.

LOG10(X)

Returns the base-10 logarithm of X:

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

POW(X,Y)

POWER(X,Y)

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

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

SQRT(X)

Returns the non-negative square root of X:

mysql> SELECT SQRT(4);
-> 2.000000
mysql> SELECT SQRT(20);
-> 4.472136

PI()

Returns the value of PI. The default shown number of decimals is 5, but MySQL internally uses the full double precession for PI.

mysql> SELECT PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116

COS()

COS(X)

Returns the cosine of X, where X is given in radians:

mysql> SELECT COS(PI());
-> -1.000000

SIN()

SIN(X)

Returns the sine of X, where X is given in radians:

mysql> SELECT SIN(PI());
-> 0.000000

TAN()

TAN(X)

Returns the tangent of X, where X is given in radians:

mysql> SELECT TAN(PI()+1);
-> 1.557408

ACOS() ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1:

mysql> SELECT ACOS(1);
        -> 0.000000
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.570796

ASIN()

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1:

mysql> SELECT ASIN(0.2);
-> 0.201358
mysql> SELECT ASIN('foo');
-> 0.000000

ATAN()

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X:

mysql> SELECT ATAN(2);
-> 1.107149
mysql> SELECT ATAN(-2);
-> -1.107149

ATAN2()

ATAN(Y,X)

ATAN2(Y,X)

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result:

mysql> SELECT ATAN(-2,2);
-> -0.785398
mysql> SELECT ATAN2(PI(),0);
-> 1.570796

COT()

COT(X)

Returns the cotangent of X:

mysql> SELECT COT(12);
-> -1.57267341
mysql> SELECT COT(0);
-> NULL

RAND()

RAND(N)

Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value:

mysql> SELECT RAND();
        -> 0.9233482386203
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND();
        -> 0.63553050033332
mysql> SELECT RAND();
        -> 0.70100469486881

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND()

This is useful to get a random sample of a set SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000.

Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.

RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.

LEAST()

LEAST(X,Y,...)

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

· If the return value is used in an INTEGER context, or all arguments are integer-valued, they are compared as integers.

· If the return value is used in a REAL context, or all arguments are real-valued, they are compared as reals.

· If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.

· In other cases, the arguments are compared as case-insensitive strings:

mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST("B","A","C");
        -> "A"

In MySQL versions prior to Version 3.22.5, you can use MIN() instead of LEAST.

GREATEST()

GREATEST(X,Y,...)

Returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST:

mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST("B","A","C");
        -> "C"

In MySQL versions prior to Version 3.22.5, you can use MAX() instead of GREATEST.

DEGREES()

DEGREES(X)

Returns the argument X, converted from radians to degrees:

mysql> SELECT DEGREES(PI());
-> 180.000000

RADIANS()

RADIANS(X)

Returns the argument X, converted from degrees to radians:

mysql> SELECT RADIANS(90);
-> 1.570796

TRUNCATE()

TRUNCATE(X,D)

Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part:

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

Starting from MySQL 3.23.51 all numbers are rounded towards zero.

If D is negative, then the whole part of the number is zeroed out:

mysql> SELECT TRUNCATE(122,-2);
-> 100

Note that as decimal numbers are normally not stored as exact numbers in computers, but as double values, you may be fooled by the following result:

rounding errors

mysql> SELECT TRUNCATE(10.28*100,0);
-> 1027

The above happens because 10.28 is actually stored as something like 10.2799999999999999.