SQL Numeric functions in SAP HANA with examples and syntax

SQL Numeric functions in SAP HANA with examples and syntax:-

The SQL Numeric Function in SAP HANA contains something different then the SQL SERVER.Below all the SQL Numeric functions are explained one by one with examples and with syntax.

1.Syntax: ABS (n): This function is used to return the absolute value of the numeric argument n.

Example: SELECT ABS(-678) AS “ABSOLUTE VALUE” FROM DUMMY
Result: 678 (Absolute values for -678)

2.ACOS (n): This function is used to return the arc-cosine, in radians, of the numeric argument n between -1 and 1.

Example: SELECT ACOS (.3) “acos” FROM DUMMY;
Result: acos: 1.2661036727794992

[AdSense-A]

3.ASIN (n): This function is used to return the arc-sine, in radians, of the numeric argument n between -1 and 1.

Example: SELECT ASIN (0.3) “asin” FROM DUMMY;
Result: asin: 0.3046926540153975

4.ATAN (n): This function is used to return the arc-tangent, in radians, of the numeric argument n. The range of n is unlimited.

Example: SELECT ATAN (0.3) “atan” FROM DUMMY;
Result: atan: 0.2914567944778671

5.ATAN2 (n, m): This function is used to return the arc-tangent, in radians, of the ratio of two numbers n and m. This produces the same result as ATAN(n/m).

Example: SELECT ATAN2 (0.5, 1.0) “atan2” FROM DUMMY;
Result: atan2: 0.4636476090008061

6.BINTOHEX (expression): This function is used to convert a binary value to a hexadecimal value.

Example: SELECT BINTOHEX(‘A’) “bintohex” FROM DUMMY;
Result: bintohex: 41

[AdSense-A]

7. BITAND (n, m): This function is used to perform an AND operation on the bits of the arguments n and m. Both n and m must be non-negative integers or varbinary. The BITAND function returns a result along argument’s type.

Example: SELECT BITAND (20, 40) “bitand” FROM DUMMY;
Result: bitand: 0

8.BITCOUNT (<expression>): This function is used to count the number of set bits of the argument <expression>. <expression> must be an integer or a varbinary. The BITCOUNT function returns an integer type.

 
Example: SELECT BITCOUNT (20) “bitcount” FROM DUMMY;
Result: bitcount: 2

9. BITNOT (expression): This function is used to perform a bitwise NOT operation on the bits of the argument <expression>. <expression>must be an integer. The BITNOT function returns a result along argument’s type.

Example: SELECT BITNOT (20) “bitnot” FROM DUMMY;
Result: bitnot: -20

10.BITOR (<expression1>, <expression2>): This function performs an OR operation on the bits of the arguments <expression1> and <expression2>. Both <expression1> and <expression2> must be non-negative integers or varbinary. The BITOR function returns a result along argument’s type.

Example: SELECT BITOR (20, 40) “bitor” FROM DUMMY;
Result: bitor: 60

11.BITSET (<target_num>, <start_bit>, <num_to_set>): This function is used to set <num_to_set> bits to 1 in <target_num> from the <start_bit> position.

Example: SELECT BITSET (‘abcd’, 1, 2) “bitset” FROM DUMMY;
Result: bitset: EBCD

12.BITUNSET (<target_num>, <start_bit>, <num_to_unset>): This function is used to set <num_to_unset> bits to 0 in <target_num> from the <start_bit> position.

Example: SELECT BITUNSET (‘abcd’, 1, 2) “bitunset” FROM DUMMY;
Result: bitunset: 2BCD

11. BITXOR (expression1, expression2): This function is used to perform an XOR operation on the bits of the arguments <expression1> and <expression2>. Both <expression1> and <expression2> must be non-negative integers or varbinary. The BITXOR function returns a result along argument’s type.

Example: SELECT BITXOR (20,40) “bitxor” FROM DUMMY;
Result: bitxor: 60

12.CEIL (n): This function is used to return the first integer that is greater or equal to the value n.

Example: SELECT CEIL (2.1) “ceiling” FROM DUMMY;
Result: ceiling: 3

13. COS (n): This function is used to return the cosine of the angle, in radians, of the argument n.

Example: SELECT COS (0.5) “cos” FROM DUMMY;
Result: cos: 0.8775825618903728

[AdSense-A]

14. COSH (n): This function is used to compute the hyperbolic cosine of the argument n.

Example: SELECT COSH (0.5) “cosh” FROM DUMMY;
Result: cosh: 1.1276259652063807

15. COT (n):  This function is used to compute the cotangent of a number n, where the argument is an angle expressed in radians.

Example: SELECT COT (0.5) “cot” FROM DUMMY;
Result: cot: 1.830487721712452

16. EXP (n): This function is used to return the result of the base of natural logarithms e raised to the power of the argument n.

Example: SELECT EXP (2.0) “exp” FROM DUMMY;
Result: exp: 7.38905609893065

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.