MySQL - SIGN() Function

The SIGN() function of MySQL accepts an integer value as a parameter and returns the sign of the given number.

  • 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.

In other words, you might already know that real numbers consist of a magnitude and a sign (known as signum) that separates them into positive and negative values. This MySQL function calculates the sign of the real value function.

Syntax

Following is the syntax of MySQL SIGN() function −

SIGN(x)

Parameters

This function takes an integer value as a parameter.

Return Value

This function returns -1 if the given value is negative, 0 if it is zero, and 1 if it is positive.

Example

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

SELECT SIGN(55475) As Result;

Output

The output for the query above is produced as given below −

Result
1

Example

Following is an another example of this function, where we are passing a negative value as a parameter.

SELECT SIGN(-9637458574) As Result;

Output

This will produce the following result −

Result
-1

Example

If we pass 0 as a parameter to this function the result will be 0 −

SELECT SIGN(0) As Result;

Output

The output is produced as follows −

Result
0

Example

We can also pass the number as a string value, to this function −

SELECT SIGN('-545752') As Result;

Output

This will produce the following result −

Result
-1

Example

In the following example, we are creating a table named STUDENTS using the CREATE statement as follows −

CREATE TABLE STUDENTS (
   ID int,
   NAME varchar(20) not null,
   AGE int not null,
   ADDRESS char (25),
   MARKS int,
   primary key (ID)
);

The below query adds 7 records into the above-created table −

INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS,MARKS) VALUES 
(1, 'Ramesh', 18, 'Ahmedabad', 80),
(2, 'Khilan', 19, 'Delhi', -10),
(3, 'Kaushik', 20, 'Kota', 85),
(4, 'Chaitali', 18, 'Mumbai', 90),
(5, 'Hardik', 18, 'Bhopal', -25),
(6, 'Komal', 21, 'Hyderabad', -34),
(7, 'Muffy', 22, 'Indore', 99);

To verify whether the records are inserted, execute the following query −

Select * From STUDENTS;

Following is the STUDENTS table −

ID NAME AGE ADDRESS MARKS
1 Ramesh 18 Ahmedabad 80
2 Khilan 19 Delhi -10
3 Kaushik 20 Kota 85
4 Chaitali 18 Mumbai 90
5 Hardik 18 Bhopal -25
6 Komal 21 Hyderabad -34
7 Muffy 22 Indore 99

Now, we are using the MySQL SIGN() function on MARKS column to determine the sign of each MARKS value −

SELECT *, SIGN(MARKS) AS SIGN FROM STUDENTS;

The output for the query above is produced as given below −

ID NAME AGE ADDRESS MARKS SIGN
1 Ramesh 18 Ahmedabad 80 1
2 Khilan 19 Delhi -10 -1
3 Kaushik 20 Kota 85 1
4 Chaitali 18 Mumbai 90 1
5 Hardik 18 Bhopal -25 -1
6 Komal 21 Hyderabad -34 -1
7 Muffy 22 Indore 99 1
Advertisements