SQL - SIGN() Function

---

The SQL SIGN() function is used to determine whether a number is positive, negative, or zero.

Assume we have a table with a column that stores the value in both negative and positive integers. Then, to detect the positive and negative numbers, we can use the SIGN() function.

The SIGN() function returns the sign of a number, in the form of an integer which could be -1, 1 or, 0.

• If the specified number is a negative value this function returns -1.

• If the specified number is a positive value this function returns 1.

• If the specified number is 0 (neither negative nor positive) this function returns 0.

Syntax

Following is the syntax of the SQL SIGN() function −

SELECT SIGN(x) AS Alias_Name;

where x can be a negative, positive, or zero integer.

Following is the SIGN() function syntax to use in the table −

SELECT SIGN(column_name) AS Aliase_Name FROM table_name;

Where column_name is the name of an integer-valued column in a table.

Example

Following is an example of the SIGN() function. In here we are passing a positive value as a parameter.

Following is the query −

SELECT SIGN(1234) AS sign_of_pos_value;

Output

Following is the output of the above SQL query, which is 1 if the value passed is positive.

+-------------------+
| sign_of_pos_value |
+-------------------+
|                 1 |
+-------------------+

Example

Following is an example of the SIGN() function. In here we are passing a negative value as a parameter.

Following is the query −

SELECT SIGN(-1234) AS sign_of_neg_value;

Output

Following is the output of the above SQL query, which is -1 if the value passed is negative.

+-------------------+
| sign_of_pos_value |
+-------------------+
|                -1 |
+-------------------+

Example

Following is an example of the SIGN() function. In here we are passing a zero as a parameter.

Following is the query −

SELECT SIGN(0) AS sign_of_zero;

Output

Following is the output of the above SQL query, which is 0 if the value passed is zero.

+--------------+
| sign_of_zero |
+--------------+
|            0 |
+--------------+

Example

In the following example, we are fetching the name and finding the account balance which employee has negative or which has positive balance from the emp_tbl. Let’s create a table named emp_tbl −

Create table emp_tbl (ID INT NOT NULL, NAME VARCHAR(20), ACCOUNT_BL NUMERIC);

Let’s insert n data into table −

insert into emp_tbl values(1, 'Raja', 1200);
insert into emp_tbl values(2, 'Vivek', 1500);
insert into emp_tbl values(3, 'Roja', -1500);
insert into emp_tbl values(4, 'Lukha', -1700);
insert into emp_tbl values(5, 'Sonal', 1800);

Let’s fetch the table details.

SELECT * FROM emp_tbl;

Following is the emp_tbl −

+----+-------+------------+
| ID | NAME  | ACCOUNT_BL |
+----+-------+------------+
|  1 | Raja  |       1200 |
|  2 | Vivek |       1500 |
|  3 | Roja  |      -1500 |
|  4 | Lukha |      -1700 |
|  5 | Sonal |       1800 |
+----+-------+------------+

Following is the query −

SELECT NAME, SIGN(ACCOUNT_BL) FROM emp_tbl;

Output

Following is the output of the above query, which shows the name and which employee has an account balance or does not. If an employee has a positive sign value, they have a positive account balance. They have no account balance if they have a negative sign value.

+-------+------------------+
| NAME  | SIGN(ACCOUNT_BL) |
+-------+------------------+
| Raja  |                1 |
| Vivek |                1 |
| Roja  |               -1 |
| Lukha |               -1 |
| Sonal |                1 |
+-------+------------------+