SQL basics- complete reference guide - part5 - Mathematical Functions

Part5: Mathematical Functions in SQL- Complete Reference

Function	Description	SYNTAX	Example
ABS	Returns the absolute value of a number.	ABS ( { int | long | decimal | double } )	SELECT ABS(-10) returns 10
ACOS	Returns the arc cosine of a value in radians.	ACOS(double)	SELECT ACOS(0.5) returns 1.0472...
ASIN	Returns the arc sine of a value in radians.	ASIN(double)	SELECT ASIN(0.5) returns 0.5236...
ATAN	Returns the arc tangent of a value in radians.	ATAN(double)	SELECT ATAN(1) returns 0.7854...
COS	Returns the cosine of an angle (radians).	COS(double)	SELECT COS(0) returns 1.0
COT	Returns the cotangent of an angle (radians).	COT(double)	SELECT COT(1) returns 0.6421...
SIN	Returns the sine of an angle (radians).	SIN(double)	SELECT SIN(0) returns 0.0
TAN	Returns the tangent of an angle (radians).	TAN(double)	SELECT TAN(0) returns 0.0
ATAN2	Returns the arc tangent of y/x, using the signs to determine the quadrant.	ATAN2(double, double)	SELECT ATAN2(1, 0) returns 1.5708...
BITAND	The bitwise AND operation.	BITAND(long, long)	SELECT BITAND(5, 3) returns 1
BITOR	The bitwise OR operation.	BITOR(long, long)	SELECT BITOR(5, 3) returns 7
BITXOR	The bitwise XOR operation.	BITXOR(long, long)	SELECT BITXOR(5, 3) returns 6
MOD	The modulo operation.	MOD(long, long)	SELECT MOD(7, 3) returns 1
CEILING	Returns the smallest integer >= the argument.	CEILING(double)	SELECT CEILING(2.3) returns 3
DEGREES	Converts radians to degrees.	DEGREES(double)	SELECT DEGREES(PI()) returns 180
EXP	Returns e raised to the power of the argument.	EXP(double)	SELECT EXP(1) returns 2.718...
FLOOR	Returns the largest integer <= the argument.	FLOOR(double)	SELECT FLOOR(2.7) returns 2
LOG	Returns the natural logarithm (base e).	LOG(double)	SELECT LOG(1) returns 0.0
LOG10	Returns the base‑10 logarithm.	LOG10(double)	SELECT LOG10(100) returns 2.0
RADIANS	Converts degrees to radians.	RADIANS(double)	SELECT RADIANS(180) returns 3.14159...
SQRT	Returns the square root.	SQRT(double)	SELECT SQRT(9) returns 3.0
PI	Returns the mathematical constant π.	PI()	SELECT PI() returns 3.14159...
POWER	Raises the first argument to the power of the second.	POWER(double, double)	SELECT POWER(2, 3) returns 8.0
RAND	Calling the function without parameter returns the next a pseudo random number. With a seed, the sequence becomes deterministic.	RAND( [ int ] )	SELECT RAND() returns 0.730...
RANDOM_UUID	Returns a new UUID with 122 pseudo random bits.	RANDOM_UUID()	SELECT RANDOM_UUID() returns 'a0eebc99-9c...'
ROUND	Rounds to a number of digits. Positive digits round to decimal places, negative to powers of ten.	ROUND(double, digitsInt)	SELECT ROUND(2.345, 2) returns 2.35
ROUNDMAGIC	This function rounds numbers in a good way, but it is slow. Uses a more mathematically correct rounding algorithm (half‑even).	ROUNDMAGIC(double)	SELECT ROUNDMAGIC(2.5) returns 2 (half-even)
SECURE_RAND	Generates an array of cryptographically secure random bytes. The argument specifies the number of bytes.	SECURE_RAND(int)	SELECT SECURE_RAND(8) returns [B@1a2b3c...
SIGN	Returns -1 if the value is smaller 0, 0 if zero, and otherwise 1.	SIGN ( { int | long | decimal | double } )	SELECT SIGN(-45) returns -1
ENCRYPT	Encrypts data using a key. The algorithm string can be 'AES', 'DES', etc.	ENCRYPT(algorithmString, keyBytes, dataBytes)	SELECT ENCRYPT('AES', '0123456789abcdef', 'my data')
DECRYPT	Decrypts data using a key. Must match the encryption algorithm and key.	DECRYPT(algorithmString, keyBytes, dataBytes)	SELECT DECRYPT('AES', '0123456789abcdef', encrypted_bytes)
HASH	Calculate the hash value using an algorithm, and repeat this process for a number of iterations. Useful for key derivation.	HASH(algorithmString, dataBytes, iterationInt)	SELECT HASH('SHA256', 'hello', 1000)
TRUNCATE	Truncates to a number of digits (to the next value closer to 0).	TRUNCATE(double, digitsInt)	SELECT TRUNCATE(2.345, 2) returns 2.34
COMPRESS	Compresses the data using the specified compression algorithm. If algorithm is omitted, default is 'LZF'.	COMPRESS(dataBytes [, algorithmString])	SELECT COMPRESS('Hello world', 'LZF')
EXPAND	Expands data that was compressed using the COMPRESS function.	EXPAND(bytes)	SELECT EXPAND(compressed_bytes)
ZERO	Returns the value 0. Useful as a placeholder when a constant zero is needed.	ZERO()	SELECT ZERO() returns 0