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

What will be the best example of the usage of this function that you will not have to use the CASE Statement. Here is example of CASE Statement usage and the same replaced with SIGN function.

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.

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

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

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.

Excellent post. It’s impressive how SIGN allows one to write much less code to get the same output.

LikeLike

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

LikeLike

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!

LikeLike

Fixed the typo thank you so much!

LikeLike

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

LikeLike

Fixed the typo thank you so much My friend!

LikeLike

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

LikeLike

Excellent suggestion – I totally agree with it!

LikeLike

Result for NumVal3 should be 0.00 in the screenshot :)

LikeLike

My I suggest another correction: NumVal3 value in the screenshot should be 0.00 :)

LikeLike

I re-ran the query and new image is now uploaded. Thanks for your attention to the details.

LikeLike

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.

LikeLike

Hi,

In that case you can just multiply the value with -1.

LikeLike

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?

LikeLike

Looks excellent!

LikeLike

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!

LikeLike

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

LikeLike

Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

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

LikeLike