What are the arithmetic and character operators in DBMS?

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.

• Unary+ - arithmetic operators {PRIOR operator}
• / arithmetic operators
• Binary + - arithmetic operator, || character operators
• All comparison operators
• NOT logical operator
• AND logical operator
• OR logical operator

Arithmetic operators

These operators are used to manipulate the numeric operands. The – operator is also used in data arithmetic.

+(unary) − Makes operant positive.

For example,

select +3 from dual;

-(unary) − Negates operand.

For example,

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.

For example,

select sal/10 from emp;

*(Multiplication) − It is a binary operator used to multiply two operands.

For example,

select sal*10 from emp;
Update emp SET sal=sal*2.5;

+ (addition) − Add two operands using the binary operator.

For example,

select 30+20;

The output will be 50.

-(subtraction) − Subtracts two operands, it is a binary operator.

For example,

select sal-200 from emp;

Character operators

They are used in expression to manipulate character strings. It is denoted as ||. It is used to concatenate character strings.

For example,

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.