CODEWITHSUNDEEP
mysqlrandom
Lemmings19
Lemmings19

Reputation: 1481

Can MySQL generate random numbers that can be either negative or positive?

Is there any way in MySQL to generate a random number that can be either positive or negative?

For example, I want a double between -1.0 and 1.0 rather than a double between 0.0 and 1.0 which is what RAND() returns.

Upvotes: 2

Views: 3348

Answers (3)

user2009750
user2009750

Reputation: 3187

Generically, FLOOR(RAND() * <ScaleFactor> + <Offset>) generates a random number with scale and offset.

So as rand() returns random number between 0 and 1, if we scale by two, new range will be 0-2, now if we shift it by -1, it should give you required result -1 To +1

Select RAND() * 2 + (-1) as MyRandomNumber

Upvotes: 2

Gryphius
Gryphius

Reputation: 78926

SELECT -1+2*RAND();

Should return a double between -1.0 and 1.0.

Upvotes: 10

Beauvais
Beauvais

Reputation: 2279

This will choose if the number should be negative or positive and return a RAND() between -1.0 and 1.0:

SELECT CASE WHEN ROUND(RAND()) = 0 THEN RAND()-1 ELSE RAND() END AS randNum

Upvotes: 1

Related Questions