Return random value from the list

By | January 12, 2018
Questions:

I’m using SQL Server 2014, and would like to take advantage of new function CHOOSE and RAND. Basically would like to return random color from the list.

Something like:

Select CHOOSE(RAND(29), 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

Is it possible?

Answers:

You have to use RAND + ROUND in following to get integers from 1 up to 29:

DECLARE @num INT = ROUND(RAND()*28,0) + 1

SELECT CHOOSE(@num, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Test

To be more accurate you can use CEILING as @GarethD commented in following:

DECLARE @num INT = CEILING(RAND()*29)

Working SQL-FIDDLE

Questions:
Answers:

You didn’t mention that you know this and I will give you one more solution in case you don’t know this way:

SELECT TOP 1 v FROM(VALUES('bg-blue'), ('bg-blue-madison'), ('bg-blue-hoki'))t(v)
ORDER BY NEWID()

Questions:
Answers:

Try this

Declare @RandVal INT
SELECT @RandVal = ABS(Checksum(NewID()) % 29) + 1
SELECT @RandVal

Select CHOOSE(@RandVal, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

Questions:
Answers:

The RAND function takes a seed value as an argument, not the maximum random value. You need to multiply the result of the random number by the maximum you need in order to get a random number in that range.

When I tested this I had to pass the random value into a variable first or it was just returning null sometimes. As mentioned by Gareth D in the comments, this is because the way the function evaluates RAND() will be called once each time a choice is checked for equality.

DECLARE @counter smallint;

SET @counter = (RAND()*28)+1;

Select @counter, CHOOSE(@counter, 'bg-blue', 'bg-blue-madison', 'bg-blue-hoki', 'bg-blue-steel', 'bg-blue-chambray',
                    'bg-green-meadow', 'bg-green', 'bg-green-seagreen', 'bg-green-turquoise', 'bg-green-haze', 'bg-green-jungle',
                    'bg-red', 'bg-red-pink', 'bg-red-sunglo', 'bg-red-intense', 'bg-red-thunderbird', 'bg-red-flamingo',
                    'bg-yellow', 'bg-yellow-gold', 'bg-yellow-casablanca', 'bg-yellow-lemon',
                    'bg-purple', 'bg-purple-plum', 'bg-purple-studio', 'bg-purple-seance',
                    'bg-grey-cascade', 'bg-grey-silver', 'bg-grey-steel', 'bg-grey-gallery') AS Colour

Leave a Reply

Your email address will not be published. Required fields are marked *