Help Center> Data Warehouse Service (DWS)> 8.1.3 Recommended> Functions and Operators> Mathematical Functions and Operators> Numeric Operators
Updated on 2024-05-31 GMT+08:00
View PDF

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 (The result is not rounded.)

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: Model (to obtain the remainder)

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: Power (exponent calculation)

In MySQL-compatible mode, this operator means exclusive or. For details, see operator # in Bit String Functions and Operators.

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: Cubic 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 shift left

Example:

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

>>

Description: Binary shift right

Example:

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