Help Center/ DataArts Fabric/ Developer Guide/ SQL Syntax Reference/ Functions and Operators/ Numeric Operation Functions and Operators/ Numeric Operators
Updated on 2025-09-18 GMT+08:00
View PDF
Share

Numeric Operators

+

Description: Addition

Example:

1
2
3
4
5
 
SELECT 2+3 AS RESULT;
 result 
--------
      5
(1 row)

-

Description: Subtraction

Example:

1
2
3
4
5
 
SELECT 2-3 AS RESULT;
 result 
--------
     -1
(1 row)

*

Description: Multiplication

Example:

1
2
3
4
5
 
SELECT 2*3 AS RESULT;
 result 
--------
      6
(1 row)

/

Description: Division (Division operator does not round off.)

When the divisor is 0, the result is NULL (compatible with Hive behavior).

Example:

1
2
3
4
5
 
SELECT 4/2 AS RESULT;
 result 
--------
      2
(1 row)

1
2
3
4
5
 
SELECT 4/3 AS RESULT;
      result      
------------------
 1.33333333333333
(1 row)

+/-

Description: Positive/Negative

Example:

1
2
3
4
5
 
SELECT -2 AS RESULT;
 result 
--------
     -2
(1 row)

%

Description: Modulus (Remainder)

Note: When the modulus is 0, the result is NULL (compatible with Hive behavior).

Example:

1
2
3
4
5
 
SELECT 5%4 AS RESULT;
 result 
--------
      1
(1 row)

@

Description: Absolute value.

Example:

1
2
3
4
5
 
SELECT @ -5.0 AS RESULT;
 result 
--------
    5.0
(1 row)

^

Description: Exponentiation (Power operation)

In MySQL compatibility mode, it functions as an XOR.

Example:

1
2
3
4
5
 
SELECT 2.0^3.0 AS RESULT;
       result       
--------------------
 8.0000000000000000
(1 row)

|/

Description: Square root.

Example:

1
2
3
4
5
 
SELECT |/ 25.0 AS RESULT;
 result 
--------
      5
(1 row)

||/

Description: Cube root.

Example:

1
2
3
4
5
 
SELECT ||/ 27.0 AS RESULT;
 result 
--------
      3
(1 row)

!

Description: Factorial

Example:

1
2
3
4
5
 
SELECT 5! AS RESULT;
 result 
--------
    120
(1 row)

!!

Description: Factorial (prefix operator)

Example:

1
2
3
4
5
 
SELECT !!5 AS RESULT;
 result 
--------
    120
(1 row)

&

Description: Binary AND

Example:

1
2
3
4
5
 
SELECT 91&15  AS RESULT;
 result 
--------
     11
(1 row)

|

Description: Binary OR

Example:

1
2
3
4
5
 
SELECT 32|3  AS RESULT;
 result 
--------
     35
(1 row)

#

Description: Binary XOR

Example:

1
2
3
4
5
 
SELECT 17#5  AS RESULT;
 result 
--------
     20
(1 row)

~

Description: Binary NOT

Example:

1
2
3
4
5
 
SELECT ~1 AS RESULT;
 result 
--------
     -2
(1 row)

<<

Description: Binary left shift

Example:

1
2
3
4
5
 
SELECT 1<<4 AS RESULT;
 result 
--------
     16
(1 row)

>>

Description: Binary right shift

Example:

1
2
3
4
5
 
SELECT 8>>2 AS RESULT;
 result 
--------
      2
(1 row)