SQL Server SIGN()

SQL Server SIGN() Function

The SQL Server SIGN() function returns the sign of a specified number. This function indicates whether the number is positive, negative, or zero.

Syntax

SELECT SIGN(number);

The SIGN() function takes a single argument:

  • number: The numeric expression for which to determine the sign.

Example SQL Server SIGN() Function Queries

Let's look at some examples of SQL Server SIGN() function queries:

1. Basic SIGN() Example

SELECT SIGN(25) AS sign_value;

This query returns the sign of the number 25. The result will be:

sign_value
-----------
1

2. SIGN() with a Negative Number

SELECT SIGN(-13.5) AS sign_value;

This query returns the sign of the number -13.5. The result will be:

sign_value
-----------
-1

3. SIGN() with Zero

SELECT SIGN(0) AS sign_value;

This query returns the sign of the number 0. The result will be:

sign_value
-----------
0

4. SIGN() with a Column

SELECT number, SIGN(number) AS sign_value
FROM numbers;

This query returns the sign of the number column for each record in the numbers table. The result will show the original number and its corresponding sign_value.

5. SIGN() with a Variable

DECLARE @num FLOAT;
SET @num = -45.67;
SELECT SIGN(@num) AS sign_value;

This query uses a variable to store a numeric value and then returns its sign. The result will be:

sign_value
-----------
-1

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SIGN() function.

Step 1: Creating a Table

This step involves creating a new table named numbers to store some sample data.

CREATE TABLE numbers (
    id INT PRIMARY KEY,
    number FLOAT
);

In this example, we create a table named numbers with columns for id and number.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the numbers table.

INSERT INTO numbers (id, number) VALUES (1, 25);
INSERT INTO numbers (id, number) VALUES (2, -13.5);
INSERT INTO numbers (id, number) VALUES (3, 0);
INSERT INTO numbers (id, number) VALUES (4, 45.67);

Here, we insert data into the numbers table.

Step 3: Using the SIGN() Function

This step involves using the SIGN() function to return the sign of the number column.

SELECT id, number, SIGN(number) AS sign_value
FROM numbers;

This query retrieves the id, number, and the sign of the number column for each row in the numbers table. The result will be:

id  number  sign_value
--- ------- -----------
1   25      1
2   -13.5   -1
3   0       0
4   45.67   1

Conclusion

The SQL Server SIGN() function is a powerful tool for returning the sign of a specified number. Understanding how to use the SIGN() function and its syntax is essential for effective numeric calculations and data processing in SQL Server.