CODEWITHSUNDEEP
sqloracle-databaserandom
dokgu
dokgu

Reputation: 6040

SQL Random String from List

I want to choose from a list of strings and assign that as the value of one of the columns for my SELECT.

Something like the following:

SELECT id, name, GET_RANDOM_TYPE('Basic', 'Silver', 'Gold', 'Premium') AS type
FROM tbl

I'm just doing some tests hence why I need this.

Upvotes: 7

Views: 17110

Answers (2)

Hart CO
Hart CO

Reputation: 34774

Not terribly familiar with oracle, but perhaps you can simply use round(dbms_random.value(1,4)) in conjunction with a CASE expression:

SELECT id,
       CASE round(dbms_random.value(1,4)) 
            WHEN 1 THEN 'Basic' 
            WHEN 2 THEN 'Silver' 
            WHEN 3 THEN 'Gold' 
            WHEN 4 THEN 'Premium' 
       END AS type
FROM table

Upvotes: 20

kevinskio
kevinskio

Reputation: 4551

Create a table with your list of values that has a number as a primary key.

Then

Select  your_text
from your_random_table
where ID = TRUNC(DBMS_RANDOM.value(1,10));

The statement above will give you any one 10 pseudo random numbers and assumes you have 10 random values in your table. It's not really random but works for testing. See here.

Upvotes: 0

Related Questions