Working with Operators
text zoom
Previous:
Getting Data with Select Statement
Operators are used in expressions or conditional statements. They show equality, inequality, or a combination of both. Mathematical operators are found in every computer language and may be familiar to you. SQL operators follow the same rules you may have learned in a math class or know from previous programming experience.
Arithmetic Operators
You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.
Operators + - When these denote a positive or negative expression, they are unary operators.
SELECT * FROM order_items
WHERE quantity = -1;
SELECT * FROM employees
WHERE -salary < 0;
When they add or subtract, they are binary operators.
SELECT hire_date
FROM employees
WHERE SYSDATE - hire_date> 365;
Operators * / (Multiply, divide) These are binary operators.
UPDATE employees
SET salary = salary * 1.1;
(Note: - Do not use two consecutive minus signs (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. You should separate consecutive minussigns with a space or a parenthesis.)
Concatenation Operator
The concatenation operator manipulates character strings and CLOB data.
Operator || Concatenates character stringsand CLOB data.
Example This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then
selects these values and concatenates them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),
col3 VARCHAR2(6), col4 CHAR(6) );
INSERT INTO tab1 (col1, col2, col3, col4)
VALUES (’abc’, ’def ’, ’ghi ’, ’jkl’);
SELECT col1||col2||col3||col4 "Concatenation"
FROM tab1;
Concatenation
------------------------
abcdef ghi jkl
Arithmetic Operators
You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.
Operators + - When these denote a positive or negative expression, they are unary operators.
SELECT * FROM order_items
WHERE quantity = -1;
SELECT * FROM employees
WHERE -salary < 0;
When they add or subtract, they are binary operators.
SELECT hire_date
FROM employees
WHERE SYSDATE - hire_date> 365;
Operators * / (Multiply, divide) These are binary operators.
UPDATE employees
SET salary = salary * 1.1;
(Note: - Do not use two consecutive minus signs (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. You should separate consecutive minussigns with a space or a parenthesis.)
Concatenation Operator
The concatenation operator manipulates character strings and CLOB data.
Operator || Concatenates character stringsand CLOB data.
Example This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then
selects these values and concatenates them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),
col3 VARCHAR2(6), col4 CHAR(6) );
INSERT INTO tab1 (col1, col2, col3, col4)
VALUES (’abc’, ’def ’, ’ghi ’, ’jkl’);
SELECT col1||col2||col3||col4 "Concatenation"
FROM tab1;
Concatenation
------------------------
abcdef ghi jkl
