All of them can be directly used in any SELECT or WHERE clause.
Mathematical Operators
Operator | Meaning | Example | Result |
---|---|---|---|
+ | addition | SELECT 10+2 | 12 |
- | subtraction | SELECT 10-2 | 8 |
* | multipication | SELECT 10*2 | 20 |
/ | division | SELECT 10/2 | 5 |
% | modulo (remainder) | SELECT 10%3 | 1 |
^ | exponentiation (associates left to right) | SELECT 2^3 | 8 |
| / | square root | | / 25.0 | 5 |
| |/ | cube root | || / 27.0 | 3 |
! | factorial | 5 ! | 120 |
SELECT 1050 * (1 + 0.10) AS "price incl GST"
Output:
Mathematical functions
mod(a, b)
Computes the remainder of a / b.
round(n, d)
Rounds n to d decimal places.
trunc(n, d)
Truncates n to d decimal places.
ceil(n)
Computes the smallest integer value not less than n.
floor(n)
Computes the largest integer value not greater than n.
abs(n)
Computes the absolute value of n.
pi()
“π” constant
SELECT pi();
SELECT mod(15, 4), round(15.67, 1),
trunc(15.67, 1), ceil(15.67),
floor(15.67), abs(-121.4)
Output:
String Operators
Item | Meaning | Example | Result |
---|---|---|---|
|| | String concatenation | SELECT 'Hello, ’ || ‘world!’ | Hello, world! |
lower(s) | Convert string s to lower case | SELECT lower(‘Hello, world!’) | hello, world! |
upper(s) | Convert string s to upper case | SELECT upper(‘Hello, world!’) | HELLO, WORLD! |
Example:
write an SQL query to list the actor_id and fullname of every Actor whose last_name begins with ‘A’.
SELECT actor_id, last_name||', '||first_name as fullname
FROM Actor
WHERE last_name LIKE 'A%'
Output:
Reference:
Mathematical Operators