SQL Server ABS() Function

SQL Server ABS() Function

The SQL Server ABS() function is used to return the absolute value of a numeric expression. This function is useful for ensuring that a number is non-negative, which can be important for calculations and comparisons.

Syntax

SELECT ABS(number);

The ABS() function takes a single argument:

  • number: The numeric expression for which to find the absolute value.

Example SQL Server ABS() Function Queries

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

1. Basic ABS() Example

SELECT ABS(-42) AS result;

This query returns the absolute value of -42. The result will be:

result
------
42

2. ABS() with a Positive Number

SELECT ABS(3.14) AS result;

This query returns the absolute value of 3.14. The result will be:

result
------
3.14

3. ABS() with a Column

SELECT value, ABS(value) AS abs_value
FROM numbers;

This query returns the absolute values of the value column for each record in the numbers table. The result will show the original value and its corresponding absolute value as abs_value.

4. ABS() with a Variable

DECLARE @myNumber DECIMAL(10, 2);
SET @myNumber = -123.45;
SELECT ABS(@myNumber) AS result;

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

result
------
123.45

Full Example

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

Step 1: Creating a Table

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

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    value DECIMAL(10, 2)
);

In this example, we create a table named example_table with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, value) VALUES (1, -42.00);
INSERT INTO example_table (id, value) VALUES (2, 3.14);
INSERT INTO example_table (id, value) VALUES (3, -123.45);

Here, we insert data into the example_table.

Step 3: Using the ABS() Function

This step involves using the ABS() function to return the absolute values of the value column.

SELECT id, value, ABS(value) AS abs_value
FROM example_table;

This query retrieves the id, value, and the absolute value of the value column for each row in the example_table. The result will be:

id  value    abs_value
--- -------- ----------
1   -42.00   42.00
2   3.14     3.14
3   -123.45  123.45

Conclusion

The SQL Server ABS() function is a powerful tool for returning the absolute value of a numeric expression. Understanding how to use the ABS() function and its syntax is essential for effective numeric calculations and data processing in SQL Server.