How to generate an even/odd random number in Oracle SQL?

Question Level: Beginner/Intermediate

Question: Generate a random even (or odd) number in the range 1..100.

Odd/Even random number generation is fairly straightforward process if you understand the math behind a simple SQL expression:

SELECT 2*(ROUND(dbms_random.value(1,100)/2,0))   even_random,
       2*(ROUND(dbms_random.value(1,100)/2,0))+1 odd_random
FROM dual

Using the same approach we can generate a random number that is a multiple of 5 (or any other number):

SELECT 5*(ROUND(dbms_random.value(1,100)/5,0))   multiple5_random
FROM dual

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s