SQL, random number

Question

I am using MariaDB, version 11.1.2, client 15.2 for OSX10.17 (x86_64).

I can generate a random number using:

SELECT FLOOR(RAND() * 50) + 1;

When run it will generate a random number between 1 and 50. What I want to do is add a range of numbers or pattern that when it selects a random number it will ignore those in the mask, something like:

SELECT FLOOR(RAND() * 50) + 1 NOT IN (5,11,21);

Answer 1

An option for mariadb:

SELECT seq
FROM seq_1_to_50
WHERE seq NOT IN (5, 11, 21)
ORDER BY RAND()
LIMIT 1

Answer 2

You can use a while loop like this

SET @x=FLOOR(RAND()*50)+1;

WHILE @x IN (5,11,21)
BEGIN
SET @x=FLOOR(RAND()*50)+1;
END