SQL Random String from List

Question

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.

Hart CO · Accepted Answer · 2016-03-07 20:36:22Z

20

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

answered Mar 7, 2016 at 20:36

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dokgu Over a year ago

Yes this works. It's the same as what Ravindra HV answered. You just happen to provide the query.

dokgu Over a year ago

nevermind what I said about ROUND. It still needs it to work. I was using > or < so it worked on my query. Since I random from 1-10.

kevinskio · Accepted Answer · 2016-03-07 20:27:54Z

0

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.

answered Mar 7, 2016 at 20:27

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

1 Comment

dokgu Over a year ago

Is there a way to avoid having to create a new table?

Collectives™ on Stack Overflow

SQL Random String from List

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related