# Re: Force randomize within a set of numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 4 Dec 2009 07:12:50 -0800 (PST)

On Dec 3, 5:01 pm, CrazyKarma <ska..._at_gmail.com> wrote:
> Here is the last one for this week..I guess :)
>
> Is there way to force the randomize within a set of number in Oracle?
> Say I have a set of integers ( 2,8,6,5)
> and I want to force randomize function to randomly pick within that
> set only.
>
> I know about the DBMS_RANDOM package, sample and seed clause but none
> help what I need to do here.
>
> any thoughts?
>
> CK

Here is another way to do it, if you do not know how many elements will be in the list.

First, we return a row from DUAL with the rows of interest with a comma appended at the start and end:
SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL; ITEMS

,2,8,6,5,54,100,67,7778,6,

Next, we need to determine the number of elements and pick an element position at random:
SELECT
ITEMS,
SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,   (TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1)) +1) SEL_ITEM
FROM
(SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL)
CONNECT BY
LEVEL<20;

```ITEMS                       NUM_ITEMS   SEL_ITEM
-------------------------- ---------- ----------
,2,8,6,5,54,100,67,7778,6,          9          6

```

Finally, we push the above SQL statement into an inline view, search for the specified number of commas according to SEL_ITEM column to determine the starting position of the element, and then search for the next comma to determine the ending position of the element: SELECT
ITEMS,
SEL_ITEM,
SUBSTR(ITEMS,INSTR(ITEMS,',',1,SEL_ITEM)+1,(INSTR(ITEMS,',', 1,SEL_ITEM+1)) - (INSTR(ITEMS,',',1,SEL_ITEM)) -1) ITEM FROM
(SELECT
ITEMS,
SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,   (TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1)) +1) SEL_ITEM
FROM
(SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL)
CONNECT BY
LEVEL<20);

```ITEMS                        SEL_ITEM ITEM
-------------------------- ---------- ----
,2,8,6,5,54,100,67,7778,6,          6 100

```
```--

You might also be able to do something like this:
SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL;

ITEMS                       PERCENT
------------------------ ----------
2,8,6,5,54,100,67,7778,6 .582165524

SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL;

ITEM_NUMBER ITEM     PERCENT
----------- ----- ----------
1 2     .104480002
2 8      .81670697
3 6     .826051929
4 5     .477132421
5 54     .89077554
6 100   .640842927
7 67    .145088893
8 7778  .252241096
9 6     .490905924

As you can see from the above, we have a problem in that the random
percent changes for each row, which will cause a problem for us if we
try to use it in a WHERE clause.

SELECT
MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
PERCENT_RANK() OVER (ORDER BY ITEM_NUMBER) PR,
ITEM_NUMBER,
ITEM,
PERCENT
FROM
(SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL);

NUM_ITEMS         PR ITEM_NUMBER ITEM     PERCENT
--------- ---------- ----------- ----- ----------
9          0           1 2     .110718377
9       .125           2 8     .306241972
9        .25           3 6     .953005936
9       .375           4 5     .033518415
9         .5           5 54    .803485415
9       .625           6 100   .456278133
9        .75           7 67     .04461405
9       .875           8 7778  .249680394
9          1           9 6     .484834331

If we now use a FIRST_VALUE analytic function, we could just retrieve
the first PERCENT value and use that in a WHERE clause:
SELECT
NUM_ITEMS,
ITEM_NUMBER,
ITEM
FROM
(SELECT
MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
ITEM_NUMBER,
ITEM,
FIRST_VALUE(PERCENT) OVER () PERCENT
FROM
(SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL))
WHERE
ITEM_NUMBER=(TRUNC(NUM_ITEMS*PERCENT)+1);

NUM_ITEMS ITEM_NUMBER ITEM
--------- ----------- ----
9           7 67

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
```
Received on Fri Dec 04 2009 - 09:12:50 CST

Original text of this message