Build 23.0.8839

# MATH Functions

## ABS ( numeric_expression )

Returns the absolute (positive) value of the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

```                      SELECT ABS(15);
-- Result: 15

SELECT ABS(-15);
-- Result: 15
```

## ACOS ( float_expression )

Returns the arc cosine, the angle in radians whose cosine is the specified float expression.

• float_expression: The float expression that specifies the cosine of the angle to be returned. Values outside the range from -1 to 1 return null.

```                      SELECT ACOS(0.5);
-- Result: 1.0471975511966
```

## ASIN ( float_expression )

Returns the arc sine, the angle in radians whose sine is the specified float expression.

• float_expression: The float expression that specifies the sine of the angle to be returned. Values outside the range from -1 to 1 return null.

```                      SELECT ASIN(0.5);
-- Result: 0.523598775598299
```

## ATAN ( float_expression )

Returns the arc tangent, the angle in radians whose tangent is the specified float expression.

• float_expression: The float expression that specifies the tangent of the angle to be returned.

```                      SELECT ATAN(10);
-- Result: 1.47112767430373
```

## ATN2 ( float_expression1 , float_expression2 )

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.

• float_expression1: The float expression that is the y-coordinate.
• float_expression2: The float expression that is the x-coordinate.

```                      SELECT ATN2(1, 1);
-- Result: 0.785398163397448
```

## CEILING ( numeric_expression ) or CEIL( numeric_expression )

Returns the smallest integer greater than or equal to the specified numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

```                      SELECT CEILING(1.3);
-- Result: 2

SELECT CEILING(1.5);
-- Result: 2

SELECT CEILING(1.7);
-- Result: 2
```

## COS ( float_expression )

Returns the trigonometric cosine of the specified angle in radians in the specified expression.

• float_expression: The float expression of the specified angle in radians.

```                      SELECT COS(1);
-- Result: 0.54030230586814
```

## COT ( float_expression )

Returns the trigonometric cotangent of the angle in radians specified by float_expression.

• float_expression: The float expression of the angle in radians.

```                      SELECT COT(1);
-- Result: 0.642092615934331
```

## DEGREES ( numeric_expression )

Returns the angle in degrees for the angle specified in radians.

• numeric_expression: The angle in radians, an expression of an indeterminate numeric data type except for the bit data type.

```                      SELECT DEGREES(3.1415926);
-- Result: 179.999996929531
```

## EXP ( float_expression )

Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.

• float_expression: The float expression.

```                      SELECT EXP(2);
-- Result: 7.38905609893065
```

## EXPR ( expression )

Evaluates the expression.

• expression: The expression. Operators allowed are +, -, *, /, ==, !=, >, <, >=, and <=.

```                      SELECT EXPR('1 + 2 * 3');
-- Result: 7

SELECT EXPR('1 + 2 * 3 == 7');
-- Result: true
```

## FLOOR ( numeric_expression )

Returns the largest integer less than or equal to the numeric expression.

• numeric_expression: The expression of an indeterminate numeric data type except for the bit data type.

```                      SELECT FLOOR(1.3);
-- Result: 1

SELECT FLOOR(1.5);
-- Result: 1

SELECT FLOOR(1.7);
-- Result: 1
```

## GREATEST(int1,int2,....)

Returns the greatest of the supplied integers.

```				SELECT GREATEST(3,5,8,10,1)
-- Result: 10
```

## HEX(value)

Returns a the equivalent hex for the input value.

• value: A string or numerical value to be converted into hex.

```				SELECT HEX(866849198);
-- Result: 33AB11AE

SELECT HEX('Sample Text');
-- Result: 53616D706C652054657874
```

## JSON_AVG(json, jsonpath)

Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

```                      SELECT JSON_AVG('[1,2,3,4,5]', '\$[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[x]');
-- Result: 3

SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[3..]');
-- Result: 4.5
```

## JSON_COUNT(json, jsonpath)

Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

```                      SELECT JSON_COUNT('[1,2,3,4,5]', '\$[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[x]');
-- Result: 5

SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[3..]');
-- Result: 2
```

## JSON_MAX(json, jsonpath)

Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

```                      SELECT JSON_MAX('[1,2,3,4,5]', '\$[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[x]');
-- Result: 5

SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[..3]');
-- Result: 4
```

## JSON_MIN(json, jsonpath)

Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

```                      SELECT JSON_MIN('[1,2,3,4,5]', '\$[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[x]');
-- Result: 1

SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[3..]');
-- Result: 4
```

## JSON_SUM(json, jsonpath)

Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.

• json: The JSON document to compute.
• jsonpath: The JSONPath used to select the nodes. [x], [2..], [..8], or [1..12] are accepted. [x] selects all nodes.

```                      SELECT JSON_SUM('[1,2,3,4,5]', '\$[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[x]');
-- Result: 15

SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '\$.test.data[3..]');
-- Result: 9
```

## LEAST(int1,int2,....)

Returns the least of the supplied integers.

```				SELECT LEAST(3,5,8,10,1)
-- Result: 1
```

## LOG ( float_expression [, base ] )

Returns the natural logarithm of the specified float expression.

• float_expression: The float expression.
• base: The optional integer argument that sets the base for the logarithm.

```                      SELECT LOG(7.3890560);
-- Result: 1.99999998661119
```

## LOG10 ( float_expression )

Returns the base-10 logarithm of the specified float expression.

• float_expression: The expression of type float.

```                      SELECT LOG10(10000);
-- Result: 4
```

## MOD(dividend,divisor)

Returns the integer value associated with the remainder when dividing the dividend by the divisor.

• dividend: The number to take the modulus of.
• divisor: The number to divide the dividend by when determining the modulus.

```				SELECT MOD(10,3);
-- Result: 1
```

## NEGATE(real_number)

Returns the opposite to the real number input.

• real_number: The real number to find the opposite of.

```				SELECT NEGATE(10);
-- Result: -10

SELECT NEGATE(-12.4)
--Result: 12.4
```

## PI ( )

Returns the constant value of pi.

```                  SELECT PI()
-- Result: 3.14159265358979
```

## POWER ( float_expression , y )

Returns the value of the specified expression raised to the specified power.

• float_expression: The float expression.
• y: The power to raise float_expression to.

```                      SELECT POWER(2, 10);
-- Result: 1024

SELECT POWER(2, -2);
-- Result: 0.25
```

Returns the angle in radians of the angle in degrees.

• float_expression: The degrees of the angle as a float expression.

```                      SELECT RADIANS(180);
-- Result: 3.14159265358979
```

## RAND ( [ integer_seed ] )

Returns a pseudorandom float value from 0 through 1, exclusive.

• seed: The optional integer expression that specifies the seed value. If seed is not specified, a seed value at random will be assigned.

```                      SELECT RAND();
-- This result may be different, since the seed is randomized
-- Result: 0.873159630165044

SELECT RAND(1);
-- This result will always be the same, since the seed is constant
-- Result: 0.248668584157093
```

## ROUND ( numeric_expression [ ,integer_length] [ ,function ] )

Returns the numeric value rounded to the specified length or precision.

• numeric_expression: The expression of a numeric data type.
• length: The optional precision to round the numeric expression to. When this is ommitted, the default behavior will be to round to the nearest whole number.
• function: The optional type of operation to perform. When the function parameter is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

```                      SELECT ROUND(1.3, 0);
-- Result: 1

SELECT ROUND(1.55, 1);
-- Result: 1.6

SELECT ROUND(1.7, 0, 0);
-- Result: 2

SELECT ROUND(1.7, 0, 1);
-- Result: 1

SELECT ROUND (1.24);
-- Result: 1.0
```

## SIGN ( numeric_expression )

Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.

• numeric_expression: The expression of an indeterminate data type except for the bit data type.

```                      SELECT SIGN(0);
-- Result: 0

SELECT SIGN(10);
-- Result: 1

SELECT SIGN(-10);
-- Result: -1
```

## SIN ( float_expression )

Returns the trigonometric sine of the angle in radians.

• float_expression: The float expression specifying the angle in radians.

```                     SELECT SIN(1);
-- Result: 0.841470984807897
```

## SQRT ( float_expression )

Returns the square root of the specified float value.

• float_expression: The expression of type float.

```                      SELECT SQRT(100);
-- Result: 10
```

## SQUARE ( float_expression )

Returns the square of the specified float value.

• float_expression: The expression of type float.

```                      SELECT SQUARE(10);
-- Result: 100

SELECT SQUARE(-10);
-- Result: 100
```

## TAN ( float_expression )

Returns the tangent of the input expression.

• float_expression: The expression of type float.

```                      SELECT TAN(1);
-- Result: 1.5574077246549
```

## TRUNC(decimal_number,precision)

Returns the supplied decimal number truncated to have the supplied decimal precision.

• decimal_number: The decimal value to truncate.
• precision: The number of decimal places to truncate the decimal number to.

```				SELECT TRUNC(10.3423,2);
-- Result: 10.34
```

## _ROW_NUMBER_()

Returns a row index as an additional column.

```				SELECT ColumnName, _ROW_NUMBER_() FROM TableName
-- Result: ColumnData, 0
ColumnData2, 1
ColumnData3, 2
```