All Products
Search

This Product

    IoT Platform:Mathematical functions

    Document Center

    IoT Platform:Mathematical functions

    Last Updated:Nov 10, 2023

    The SQL analysis feature provides common mathematical functions that you can use to develop data. You can select mathematical functions based on your business requirements to compute data or convert data types. This topic describes the syntax, parameters, and examples of mathematical functions supported by the SQL analysis feature to facilitate development.

    The following table describes the mathematical functions that are supported by the SQL analysis feature.

    Function

    Description

    ABS

    Calculates the absolute value.

    ACOS

    Calculates the arccosine.

    ATAN2

    Calculates the arctangent of expr1 and expr2.

    ASIN

    Calculates the arcsine.

    ATAN

    Calculates the arctangent.

    CEIL

    Rounds up a number and returns the nearest integer.

    CONV

    Converts a number from one number system to another number system.

    CORR

    Calculates the Pearson correlation coefficient.

    COS

    Calculates the cosine.

    COSH

    Calculates the hyperbolic cosine.

    COT

    Calculates the cotangent.

    DEGREES

    Converts a radian value into a degree.

    E

    Calculates the value of e.

    EXP

    Calculates the exponential value.

    FLOOR

    Rounds down a number and returns the nearest integer.

    FORMAT_NUMBER

    Converts a number into a string in the specified format.

    LN

    Calculates the natural logarithm.

    LOG

    Calculates the logarithm.

    LOG10

    Calculates the logarithm of a number whose base number is 10.

    LOG2

    Calculates the logarithm of a number whose base number is 2.

    NEGATIVE

    Returns the negative value of an expression.

    PI

    Calculates the value of π.

    POSITIVE

    Returns the value of an expression.

    POW

    Calculates the nth power of a value.

    RADIANS

    Converts a degree into a radian value.

    RAND

    Returns a random number.

    ROUND

    Returns a value rounded to the specified decimal place.

    SHIFTLEFT

    Shifts a value left by a specified number of places.

    SHIFTRIGHT

    Shifts a value right by a specified number of places.

    SHIFTRIGHTUNSIGNED

    Shifts an unsigned value right by a specific number of places.

    SIGN

    Returns the sign of the input value.

    SIN

    Calculates the sine.

    SINH

    Calculates the hyperbolic sine.

    SQRT

    Calculates the square root.

    TAN

    Calculates the tangent.

    TANH

    Calculates the hyperbolic tangent.

    ABS

    • Syntax 

      bigint|double|decimal abs(<number>)

    • Description

      Calculates the absolute value of number.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, or DECIMAL type. If the input value is of the STRING type, the value is implicitly converted into a value of the DOUBLE type before calculation.

      Note

      If the input value is of the BIGINT type and is greater than the maximum value of the BIGINT type, a value of the DOUBLE type is returned. However, the precision may be lost.

    • Return value

      The data type of the return value varies based on the data type of the input parameter. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE, BIGINT, or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value null is returned. 
select abs(null);
-- The value 1 is returned. 
select abs(-1);
-- The value 1.2 is returned. 
select abs(-1.2);
-- The value 2.0 is returned. 
select abs("-2");
-- The value 1.2232083745629837E32 is returned. 
select abs(122320837456298376592387456923748);
-- Calculate the absolute value of the id field in tbl1. The following example shows how to call an ABS function in SQL statements. Other built-in functions, except window functions and aggregate functions, are used in a similar manner. 
select abs(id) from tbl1;

    ACOS

    • Syntax 

      double|decimal acos(<number>)

    • Description

      Calculates the arccosine of number.

    • Parameters

      number: required. The value ranges from -1 to 1. A value of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      The data type of the return value varies based on the data type of the input parameter. The value ranges from 0 to π. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number does not fall within the range of -1 to 1, null is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 0.5155940062460905 is returned. 
select acos("0.87");
-- The value 1.5707963267948966 is returned. 
select acos(0);
-- The value null is returned. 
select acos(null);

    ATAN2

    • Syntax 

      double atan2(<expr1>, <expr2>)

    • Description

      Calculates the arctangent of expr1 and expr2.

    • Parameters

      • expr1: required. The value is of the DOUBLE type. If the input value is of the STRING, BIGINT, or DECIMAL type, it is implicitly converted into a value of the DOUBLE type before calculation.

      • expr2: required. A value of the DOUBLE type. If the input value is of the STRING, BIGINT, or DECIMAL type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE type is returned. The return value ranges from -π/2 to π/2. If the value of expr1 or expr2 is null, null is returned.

    • Examples 

      -- The value 0.0 is returned. 
select atan2(0, 0);

    ASIN

    • Syntax 

      double|decimal asin(<number>)

    • Description

      Calculates the arcsine of number.

    • Parameters

      number: required. The value ranges from -1 to 1. A value of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      The data type of the return value varies based on the data type of the input parameter. The return value ranges from -π/2 to π/2. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number does not fall within the range of -1 to 1, null is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 1.5707963267948966 is returned. 
select asin(1);
-- The value -1.5707963267948966 is returned. 
select asin(-1);
-- The value null is returned. 
select asin(null);

    ATAN

    • Syntax 

      double atan(<number>)

    • Description

      Calculates the arctangent of number.

    • Parameters

      number: required. A value of the DOUBLE type. If the input value is of the STRING, BIGINT, or DECIMAL type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE type is returned. The return value ranges from -π/2 to π/2. If the value of number is null, null is returned.

    • Examples 

      • -- The value 0.7853981633974483 is returned. 
select atan(1);
-- The value -0.7853981633974483 is returned. 
select atan(-1);
-- The return value is null. 
select atan(null);

    CEIL

    • Syntax 

      bigint ceil(<value>)

    • Description

      Rounds up value and returns the nearest integer.

    • Parameters

      value: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the BIGINT type is returned. If the value of number is null, null is returned.

    • Examples 

      • -- The value 2 is returned. 
select ceil(1.1);
-- The value -1 is returned. 
select ceil(-1.1);
-- The return value is null. 
select ceil(null);

    CONV

    • Syntax 

      string conv(<input>, bigint <from_base>, bigint <to_base>)

    • Description

      Converts a number from one number system to another number system.

    • Parameters

      • input: required. The value is the integer that you want to convert, which is of the STRING type. If the input value is of the BIGINT or DOUBLE type, it is implicitly converted into a value of the STRING type before calculation.

      • from_base and to_base: required. The values of these parameters are decimal numbers. The values can be 2, 8, 10, or 16. If the input value is of the STRING or DOUBLE type, it is implicitly converted into a value of the BIGINT type before calculation.

    • Return values

      A value of the STRING type is returned. The value null is returned in the following scenarios:

      • If the value of input, from_base, or to_base is null, null is returned.

      • The conversion process runs at 64-bit precision. If an overflow occurs, null is returned.

      • If the value of input is a negative value, null is returned. If the value of input is a decimal, it is converted into an integer before the conversion of number systems. The decimal part is discarded.

    • Examples

      • -- The value 12 is returned. 
select conv('1100', 2, 10);
-- The value C is returned. 
select conv('1100', 2, 16);
-- The value 171 is returned. 
select conv('ab', 16, 10);
-- The value AB is returned. 
select conv('ab', 16, 16);
-- The return value is null. 
select conv('1100', null, 10);

    CORR

    • Syntax 

      double corr(<col1>, <col2>)

    • Description

      Calculates the Pearson correlation coefficient for two columns of data.

    • Parameters

      col1 and col2: required. The names of the two columns for which the Pearson correlation coefficient is calculated. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, or DECIMAL type. Data in the col1 and col2 columns can be of different data types.

    • Return value

      A value of the DOUBLE type is returned. If an input column has a null value in a row, the row is not included in the calculation.

    COS

    • Syntax 

      double|decimal cos(<number>)

    • Description

      Calculates the cosine of number, which is a radian value.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples 

      • -- The value 2.6794896585028633e-8 is retuned. 
select cos(3.1415926/2);
-- The value -0.9999999999999986 is returned. 
select cos(3.1415926);
-- The return value is null. 
select cos(null);

    COSH

    • Syntax 

      double|decimal cosh(<number>)

    • Description

      Calculates the hyperbolic cosine of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 2.5091784169949913 is returned. 
select cosh(3.1415926/2);
-- The return value is null. 
select cosh(null);

    COT

    • Syntax 

      double|decimal cot(<number>)

    • Description

      Calculates the cotangent of number, which is a radian value.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The return value is 2.6794896585028643E-8. 
select cot(3.1415926/2);
-- The return value is null. 
select cot(null);

    DEGREES

    • Syntax 

      double degrees(<number>)

    • Description

      This function converts a radian value into a degree.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

    • Return value

      A value of the DOUBLE type is returned. If the value of number is null, null is returned.

    • Examples

      • -- The return value is 90.0. 
select degrees(1.5707963267948966);
-- The return value is 0.0. 
select degrees(0);
-- The return value is null. 
select degrees(null);

    E

    • Syntax 

      double e()

    • Description

      Calculates the value of e.

    • Return value

      A value of the DOUBLE type is returned.

    • Examples 

      -- The value 2.718281828459045 is returned. 
select e();

    EXP

    • Syntax 

      double|decimal exp(<number>)

    • Description

      Calculates the exponential value of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 4.810477252069109 is returned. 
select exp(3.1415926/2);
-- The return value is null. 
select exp(null);

    FLOOR

    • Syntax 

      bigint floor(<number>)

    • Description

      Rounds down the value of number and returns the nearest integer that is no greater than the value of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the BIGINT type is returned. If the value of number is null, null is returned.

    • Examples

      • -- The return value is 1. 
select floor(1.2);
-- The return value is 0. 
select floor(0.1);
-- The return value is -2. 
select floor(-1.2);
-- The return value is -1. 
select floor(-0.1);
-- The return value is 0. 
select floor(0.0);
-- The return value is 0. 
select floor(-0.0);
-- The return value is null. 
select floor(null);

    FORMAT_NUMBER

    • Syntax 

      string format_number(float|double|decimal <expr1>, <expr2>)

    • Description

      This function converts a number into a string in a specified format.

    • Parameters

      • expr1: required. This parameter specifies the expression that you want to convert to a specified format. A value of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

      • expr2: required. This parameter specifies the format of the expression after the conversion. The parameter can specify the number of decimal places that you want to retain. The parameter can also be in a format similar to #,###,###.##.

    • Return value

      A value of the STRING type is returned. The return value varies based on the following rules:

      • If expr2 is greater than 0, the value is rounded to the specified place after the decimal point.

      • If expr2 is equal to 0, the value has no decimal point or decimal part.

      • If expr2 is less than 0 or greater than 340, an error is returned.

      • If the value of expr1 or expr2 is null, null is returned.

    • Examples

      • -- The value 5.230 is returned. 
select format_number(5.230134523424545456,3);
-- The value 12,332.123 is returned. 
select format_number(12332.123456, '#,###,###,###.###');
-- The return value is null. 
select format_number(null,3);

    ISNAN

    • Syntax 

      boolean isnan(<expr>)

    • Description

      Checks whether the value of expr is NaN.

    • Parameters

      expr: required. A value of the DOUBLE type. If the input value is of the STRING, BIGINT, or DECIMAL type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      • If the value of expr is NaN, True is returned. Otherwise, False is returned.

      • If the value of expr is null, False is returned.

    • Examples

      -- The value False is returned. 
SELECT isnan(100.1);

    LN

    • Syntax 

      double|decimal ln(<number>)

    • Description

      Calculates the natural logarithm of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is a negative value or 0, null is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 1.144729868791239 is returned. 
select ln(3.1415926);
-- The return value is null. 
select ln(null);

    LOG

    • Syntax 

      double log(<base>, <x>)

    • Description

      Calculates the logarithm of x whose base number is the value of base.

    • Parameters

      • base: required. The base number. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

      • x: required. The value for which the logarithm is calculated. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE type is returned. The return value varies based on the following rules:

      • If the value of base or x is null, null is returned.

      • If the value of base or x is a negative value or 0, null is returned.

      • If the value of base is 1, null is returned. The value 1 causes division by zero.

    • Examples

      • -- The value 4.0 is returned. 
select log(2, 16);
-- The return value is null. 
select log(2, null);

    LOG10

    • Syntax 

      double log10(<number>)

    • Description

      Calculates the logarithm of number whose base number is 10.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

    • Return value

      A value of the DOUBLE type is returned. If the value of number is 0, a negative value, or null, null is returned.

    • Examples

      • -- The return value is null. 
select log10(null);
-- The return value is null. 
select log10(0);
-- The value 0.9030899869919435 is returned. 
select log10(8);

    LOG2

    • Syntax 

      double log2(<number>)

    • Description

      Calculates the logarithm of number whose base number is 2.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

    • Return value

      A value of the DOUBLE type is returned. If the value of number is 0, a negative value, or null, null is returned.

    • Examples

      • -- The return value is null. 
select log2(null);
-- The return value is null. 
select log2(0);
-- The value 3.0 is returned. 
select log2(8);

    NEGATIVE

    • Syntax 

      TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL negative(TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL <expr>)

    • Description

      Returns the negative value of expr.

    • Parameters

      expr: required. The value is an expression of the TINYINT, SMALLINT, INT, BIGINT, DOUBLE, or DECIMAL type.

    • Return value

      The negative value of expr is returned.

    • Examples

      -- The value 1 is returned. 
SELECT negative(1);

    PI

    • Syntax 

      double pi()

    • Description

      This function calculates the value of π.

    • Return value

      A value of the DOUBLE type is returned.

    • Examples 

      -- The value 3.141592653589793 is returned. 
select pi();

    POSITIVE

    • Syntax 

      TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL positive(TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL <expr>)

    • Description

      Returns the value of expr.

    • Parameters

      expr: required. The value is an expression of the TINYINT, SMALLINT, INT, BIGINT, DOUBLE, or DECIMAL type.

    • Return value

      The value of expr is returned.

    • Examples

      -- The return value is 1. 
SELECT positive(1);

    POW

    • Syntax 

      double|decimal pow(<x>, <y>)

    • Description

      Calculates the yth power of x, namely, x^y.

    • Parameters

      • x: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

      • y: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If x or y is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If x or y is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of x or y is null, null is returned.

    • Examples

      • -- The value 65536.0 is returned. 
select pow(2, 16);
-- The return value is null. 
select pow(2, null);

    RADIANS

    • Syntax 

      double radians(<number>)

    • Description

      This function converts a degree into a radian value.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

    • Return value

      A value of the DOUBLE type is returned. If the value of number is null, null is returned.

    • Examples 

      • -- The return value is 1.5707963267948966. 
select radians(90);
-- The value 0.0 is returned. 
select radians(0);
-- The return value is null. 
select radians(null);

    RAND

    • Syntax

      double rand(bigint <seed>)

    • Description

      Returns a random number of the DOUBLE type. The value ranges from 0 to 1.

    • Parameters

      seed: optional. The value is of the BIGINT type. This parameter specifies the random seed that determines the start point of a random number sequence.

      Note

      You can use seed to determine a random number sequence. After the random number sequence is determined by seed, the return value of this function is fixed. If the execution environment is the same and the seed value remains unchanged, the return value is the same. If you want to obtain different results, you must modify the seed value.

    • Return value

      A value of the DOUBLE type is returned.

    • Examples

      -- The value 4.7147460303803655E-4 is returned. 
select rand();
-- The value 0.7308781907032909 is returned. 
select rand(1);

    ROUND

    • Syntax 

      double|decimal round(<number>[, bigint <decimal_places>])

    • Description

      Returns a number that is rounded to a specified decimal place.

    • Parameters

      • number: required. A value of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

      • decimal_places: optional. The value is a constant of the BIGINT type. This parameter specifies the decimal place to which the number is rounded. If you do not configure this parameter, the number is rounded to the ones place. The default value is 0.

        Note

        The value of decimal_places can be a negative value. A negative value indicates counting from the decimal point to the left, and the decimal part is excluded. If the value of decimal_places exceeds the length of the integer part, 0 is returned.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If decimal_places is not of the BIGINT type, an error is returned.

      • If the value of number or decimal_places is null, null is returned.

    • Examples 

      • -- The value 125.0 is returned. 
select round(125.315);
-- The value 125.3 is returned. 
select round(125.315, 1);
-- The value 125.32 is returned. 
select round(125.315, 2);
-- The value 125.315 is returned. 
select round(125.315, 3);
-- The value -125.32 is returned. 
select round(-125.315, 2);
-- The value 100.0 is returned. 
select round(123.345, -2);
-- The return value is null. 
select round(null);
-- The value 123.345 is returned. 
select round(123.345, 4);
-- The value 0.0 is returned. 
select round(123.345, -4);

    SHIFTLEFT

    • Syntax 

      int shiftleft(tinyint|smallint|int <number1>, int <number2>)
bigint shiftleft(bigint <number1>, int <number2>)

    • Description

      Shifts a value left by a specific number of places (<<).

    • Parameters

      • number1: required. The value is of the TINYINT, SMALLINT, INT, or BIGINT type.

      • number2: required. The value is of the INT type.

    • Return value

      A value of the INT or BIGINT type is returned. The return value varies based on the following rules:

      • If the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.

      • If the value of number2 is not of the INT type, an error is returned.

      • If the value of number1 or number2 is null, null is returned.

    • Examples

      • -- The value 4 is returned. The following statement shifts the binary value of 1 two places to the left (1<<2,0001 shifted to be 0100). 
select shiftleft(1,2); 
-- The value 32 is returned. The following statement shifts the binary value of 4 three places to the left (4<<3,0100 shifted to be 100000). 
select shiftleft(4,3);
-- The return value is null. 
select shiftleft(null,2);

    SHIFTRIGHT

    • Syntax 

      int shiftright(tinyint|smallint|int <number1>, int <number2>)
bigint shiftright(bigint <number1>, int <number2>)

    • Description

      Shifts a value right by a specific number of places (>>).

    • Parameters

      • number1: required. The value is of the TINYINT, SMALLINT, INT, or BIGINT type.

      • number2: required. The value is of the INT type.

    • Return value

      A value of the INT or BIGINT type is returned. The return value varies based on the following rules:

      • If the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.

      • If the value of number2 is not of the INT type, an error is returned.

      • If the value of number1 or number2 is null, null is returned.

    • Examples

      • -- The return value is 1. Shift the binary value of 4 two places to the right (4>>2, 1000 shifted to be 0001). 
select shiftright(4,2);
-- The value 4 is returned. The following statement shifts the binary value of 32 three places to the right (32>>3,100000 shifted to be 0100). 
select shiftright(32,3);
-- The return value is null. 
select shiftright(null,3);

    SHIFTRIGHTUNSIGNED

    • Syntax 

      int shiftrightunsigned(tinyint|smallint|int <number1>, int <number2>)
bigint shiftrightunsigned(bigint <number1>, int <number2>)

    • Description

      Shifts an unsigned value right by a specific number of places (>>>).

    • Parameters

      • number1: required. The value is an integer of the TINYINT, SMALLINT, INT, or BIGINT type.

      • number2: required. The value is an integer of the INT type.

    • Return value

      A value of the INT or BIGINT type is returned. The return value varies based on the following rules:

      • If the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.

      • If the value of number2 is not of the INT type, an error is returned.

      • If the value of number1 or number2 is null, null is returned.

    • Examples

      • -- The return value is 2. The following statement shifts the binary unsigned value of 8 two places to the right (8>>>2,1000 shifted to be 0010). 
select shiftrightunsigned(8,2);
-- The value 1073741820 is returned. The following statement shifts the binary value of -14 two places to the right (-14>>>2, 11111111 11111111 11111111 11110010 shifted to be 00111111 11111111 11111111 11111100). 
select shiftrightunsigned(-14,2);
-- The return value is null. 
select shiftrightunsigned(-14,null);

    SIGN

    • Syntax 

      double sign(<number>)

    • Description

      Returns the sign of the input value.

    • Parameters

      number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.

    • Return value

      A value of the DOUBLE type is returned. The return value varies based on the following rules:

      • If the value of number is a positive value, 1.0 is returned.

      • If the value of number is a negative value, -1.0 is returned.

      • If the value of number is 0, 0.0 is returned.

      • If the value of number is null, null is returned.

    • Examples 

      • -- The value -1.0 is returned. 
select sign(-2.5);
-- The value 1.0 is returned. 
select sign(2.5);
-- The value 0.0 is returned. 
select sign(0);
-- The return value is null. 
select sign(null);

    SIN

    • Syntax 

      double|decimal sin(<number>)

    • Description

      Calculates the sine of number, which is a radian value.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value -0.3048106211022167 is returned. 
select sin(60);
-- The return value is null. 
select sin(null);

    SINH

    • Syntax 

      double|decimal sinh(<number>)

    • Description

      Calculates the hyperbolic sine of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 5.343237290762231E12 is returned. 
select sinh(30);
-- The return value is null. 
select sinh(null);

    SQRT

    • Syntax 

      double|decimal sqrt(<number>)

    • Description

      Calculates the square root of the value of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. The value must be greater than 0. If the value is less than 0, null is returned. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The return value is 2.0. 
select sqrt(4);
-- The return value is null. 
select sqrt(null);

    TAN

    • Syntax

      double|decimal tan(<number>)

    • Description

      Calculates the tangent of number, which is a radian value.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value -6.405331196646276 is returned. 
select tan(30);
-- The return value is null. 
select tan(null);

    TANH

    • Syntax 

      double|decimal tanh(<number>)

    • Description

      Calculates the hyperbolic tangent of number.

    • Parameters

      number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.

    • Return value

      A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

      • If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

      • If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

      • If the value of number is null, null is returned.

    • Examples

      • -- The value 1.0 is returned. 
select tanh(30);
-- The return value is null. 
select tanh(null);