SQL operators manipulate individual data items and return a result. The data items are called operands. SQL supports unary, binary and set operators.
Let’s see the levels of SQL operator precedence from higher to lower.
These operators are used to manipulate the numeric operands. The – operator is also used in data arithmetic.
+(unary) − Makes operant positive.
select +3 from dual;
-(unary) − Negates operand.
select * from items where itemsold=-1; Select * from emp where -sal<0;
/ (Division) − It is a binary operator which is used to divide numbers and dates.
select sal/10 from emp;
*(Multiplication) − It is a binary operator used to multiply two operands.
select sal*10 from emp; Update emp SET sal=sal*2.5;
+ (addition) − Add two operands using the binary operator.
The output will be 50.
-(subtraction) − Subtracts two operands, it is a binary operator.
select sal-200 from emp;
They are used in expression to manipulate character strings. It is denoted as ||. It is used to concatenate character strings.
select the name of the employee is:’ || ename from emp;
Concatenating character strings
We can concatenate character string with the following result −
Concatenating two-character strings result in another character string. SQL preserves trailing blanks in character strings by concatenation, regardless of the strings datatype. SQL provides the CONCAT character function as an alternative to the vertical bar operator.
SQL treats zero-length character strings as nulls. When you concatenate a zero-length character string with another operand the result is always the other operand. A null value can only result from the concatenation of two null strings.