Yesterday I received an email from a friend asking how do SIGN function works. Well SIGN Function is very fundamental function. It will return the value 1, -1 or 0. If your value is negative it will return you negative -1 and if it is positive it will return you positive +1. Let us start with a simple small example.
DECLARE @IntVal1 INT, @IntVal2 INT,@IntVal3 INT
DECLARE @NumVal1 DECIMAL(4,2), @NumVal2 DECIMAL(4,2),@NumVal3 DECIMAL(4,2)
SET @IntVal1 = 9; SET @IntVal2 = -9; SET @IntVal3 = 0;
SET @NumVal1 = 9.0; SET @NumVal2 = -9.0; SET @NumVal3 = 0.0;
SELECT SIGN(@IntVal1) IntVal1,SIGN(@IntVal2) IntVal2,SIGN(@IntVal3) IntVal3
SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal3) NumVal3Â Â
The above function will give us following result set.
You will notice that when there is positive value the function gives positive values and if the values are negative it will return you negative values. Also you will notice that if the data type is  INT the return value is INT and when the value passed to the function is Numeric the result also matches it. Not every datatype is compatible with this function.  Here is the quick look up of the return types.
- bigint -> bigint
- int/smallint/tinyint -> int
- money/smallmoney -> money
- numeric/decimal ->Â numeric/decimal
- everybody else -> float
USE tempdb
GO
CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-22 16:15', '2012-06-20 16:15'
UNION ALL
SELECT '2012-06-24 16:15', '2012-06-22 16:15'
UNION ALL
SELECT '2012-06-22 16:15', '2012-06-22 16:15'
GO
-- Using Case Statement
SELECT CASE
WHEN DATEDIFF(d,Date1,Date2) > 0 THEN 1
WHEN DATEDIFF(d,Date1,Date2) < 0 THEN -1
ELSE 0 END AS Col
FROM TestTable
GO
-- Using SIGN Function
SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO
This was interesting blog post for me to write. Let me know your opinion.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
18 Comments. Leave new
Excellent post. It’s impressive how SIGN allows one to write much less code to get the same output.
Sign() can also be used it calculate an equivalent alternative to the ABS() function. Note that the last two columns have the same values.:
USE tempdb
CREATE TABLE T ( N INT )
INSERT INTO T VALUES ( -42), (-11), (0), (NULL), (17), (2)
SELECT N,
N * SIGN(N) AS NxSignN,
ABS(N) AS AbsN
FROM T
DROP TABLE T
GO
Think you might have a small typo, though the results you have portrayed are correct.
This:
SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal2) NumVal3
Should be:
SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal3) NumVal3
As always thanks so much for all of your posts, they have helped me come a long way!
Thanks for writing about this function. I have learnt a new function. But seems you have a typo in the second SELECT statement:
SIGN(@NumVal2) NumVal3 => SIGN(@NumVal3) NumVal3
Sign can also be use in
i)When we have to find if table contain any rowcount or not.
Select sign(@@rowcount)
Select sign(count(*) from emp
ii) IsNullOrEmpty(equivalent function of c#)
CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
RETURNS BIT
AS
BEGIN
return SIGN(LEN(isnull(@text,”)))
END
GO
Result for NumVal3 should be 0.00 in the screenshot :)
My I suggest another correction: NumVal3 value in the screenshot should be 0.00 :)
There is a kind of “inverse” function of the SIGN function? For example, if the value is a positive number, it returns -1, and if it is a negative number, it returns 1.
Brilliant.
I have to run a report where all values must be positive. Now, instead of:
UPDATE MyTable
SET Amount =
CASE
WHEN Amount < 0 THEN
(Amount * -1) * Currency
ELSE
Amount * Currency
END
I can use:
UPDATE MyTable
SET Amount = (Amount * SIGN(Amount)) * Currency
Seems to run quicker too. Any comment on that?
Hi! This is SQL statement found in SQL Profiler..
SELECT SUM(amount_book * -SIGN(cleared_type -0.5)) FROM cminpdtl WHERE date_applied <= 734760 AND cash_acct_code = '15080202ADMNHQIP002'
May I know what is the use of -SIGN(fieldname -0.5)?
Why must use -0.5 and not just the fieldname will do? cleared_type is just a value either 0 or 1.
Thank you very much in advance!
SUM( DECODE( SIGN(DAYDIFF(TransactionReceivableDetail.Receivable.AssignedPayment.CreatedOn,TempAttributesRescission.RescissionPaymentAsofDate)),1,0,TransactionReceivableDetail.Receivable.AssignedPayment.PaymentAmount))
this query is returning a null value can u explain
Hi Pinal,
Thanks but how can we use (‘^’) sign in sql server like I want to calculate below formula in sql.
Cost price x (1- Dep Rate%) ^ (Date of WDV Calculation – Date of Purchase + 1)/365
In SQL 2008 I couldn’t use this formula directly.
Can you please help me how can we use above formula in SQL 2008 ?
Thanks,
Sagar