Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Numeric Functions and Operators/ Numeric Operators
Updated on 2024-09-02 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 (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: modulo (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)