Generating only positive random numbers in SQLite

Question

In SQLite

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

You can get an random with maximum value n with Select random() % n ;

But that still generates negative numbers. How can I generate only positive random numbers in SQLite ?

Answer 1

Use the ABS() (absolute value) function:

SELECT ABS(RANDOM() % N)

Note that:

If X is the integer -9223372036854775808 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two's complement value.

Answer 2

For generating positive(including zero) random number with only upper bound, use

SELECT ABS(RANDOM() % N)

For generating positive and non-zero random number, use

SELECT ABS(RANDOM()) % (HIGH - LOW) + LOW

HIGH - represents upper bound

LOW - represents lower bound.