To generate either +1 or -1 randomly within the context of a query

Transact-SQL
1

I tried to find such thing from net but I could not.

Here is my try

declare @t table (number int primary keY)
insert @t values(1)
, (2),(3),(4),(5),(6),(7),(8),(9),(10)

select  number
 , random_positive_or_negative_1 = 
           case when right(convert(varchar,RAND(CAST( NEWID() AS varbinary ))),1) 
          in (0,2,4,6,8) then 1 else -1 end
from @t

I am not happy about
case when right(convert(varchar,RAND(CAST( NEWID() AS varbinary ))),1)
in (0,2,4,6,8) then 1 else -1 end
and also not sure if it is reliable.

2

You didn't say what about it is that you are unhappy about it. You could do the following:

CASE WHEN ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))%2=0 THEN -1 ELSE 1 END

1 Like
3

@JamesK

So , no need of rand().

Now I am happy.