PL/SQL Generate Random String from fixed set of characters

Question

How do I generate a random string in PL/SQL from a fixed set of characters? For example: I want to generate an alphanumeric string of length 10 which excludes certain characters and digits such as I,O,0 etc. I could not find a way to seed DBMS_RANDOM.STRING() with the required set. Is there a built in way in PL/SQl to do this?

Thanks.

Answer 1

You could write such a function yourself - function that accepts particular set of characters mixes it up and returns the result. Here is an example. In this example listagg function is used which is available only in Oracle 11G R2 and higher.

1. Here is the simple function:

   SQL> create or replace function RandomString(p_Characters varchar2, p_length number)
     2  return varchar2
     3  is
     4    l_res varchar2(256);
     5  begin
     6    select substr(listagg(substr(p_Characters, level, 1)) within group(order by dbms_random.value), 1, p_length)
     7      into l_res
     8      from dual
     9    connect by level <= length(p_Characters);
    10    return l_res;
    11  end;
    12  /
   
    Function created
   

2. And here is how it works:

   SQL> select randomstring('1234567abcdefg', 10) res
     2    from dual
     3  connect by level <= 10
     4  /
   
   RES
   ----------
   caedg54f67
   f35eca6gb4
   4ae6f1c37b
   1c436g7a2b
   16e357bd2a
   564gca23ef
   d57c21bg36
   3gd62b4c1e
   31ea5cb472
   fe152bdga6
   
   10 rows selected