How does one generate an upper and lowercase alphanumeric random string from oracle?
I have used select DBMS_RANDOM.STRING('x', 10) from dual to generate uppercase alphanumeric characters
and select DBMS_RANDOM.STRING('a', 10) from dual to generate uppercase and lowercase alpha characters
...but I'd like a function that does both upper and lower case, and alpha and numeric characters.
Also, bonus points (or just upvotes) if you can think of good reasons why Oracle didn't implement this?
You can make your own function. This is one option:
create or replace function random_str(v_length number) return varchar2 is
my_str varchar2(4000);
begin
for i in 1..v_length loop
my_str := my_str || dbms_random.string(
case when dbms_random.value(0, 1) < 0.5 then 'l' else 'x' end, 1);
end loop;
return my_str;
end;
/
select random_str(30) from dual;
RANDOM_STR(30)
--------------------------------------------------------------------------------
pAAHjlh49oZ2xuRqVatd0m1Pv8XuGs
You might want to adjust the 0.5 to take into account the different pool sizes - 26 for l vs. 36 for x. (.419354839?). You could also use value() and pass in the start and end range of the character values, but that would be character-set specific.
As to why... do Oracle need a reason? The use of x might suggest that it was originally hexadecimal and was expanded to include all upper-case, without it occurring to them to add a mixed-case version at the same time.
Try this,
with
r as (
select
level lvl,
substr(
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
mod(abs(dbms_random.random), 62)+1, 1) a
from dual connect by level <= 10
)
select
replace(sys_connect_by_path(a, '/'), '/') random_string
from r
where lvl = 1
start with lvl = 10
connect by lvl + 1 = prior lvl
;
Output,
FOps2k0Pcy
CREATE OR REPLACE FUNCTION fn_mac RETURN varchar2 IS
w number :=0;
a varchar2(10);
b varchar2(50);
x number :=0;
y number :=0;
z number :=0;
c varchar2(50);
result varchar2(20);
BEGIN
select round(dbms_random.value(1,99))into w from dual;
SELECT upper(dbms_random.string('A', 2))into a FROM dual;
SELECT round(dbms_random.value(1, 9)) into x FROM dual;
SELECT upper(dbms_random.string('A', 4)) into b FROM dual;
SELECT round(dbms_random.value(1, 9)) into y FROM dual;
SELECT upper(dbms_random.string('A', 1)) into c FROM dual;
SELECT round(dbms_random.value(1, 9)) into z FROM dual;
result :=( to_char(w) ||a|| to_char(x)|| b|| to_char(y)|| c ||to_char(z)) ;
DBMS_OUTPUT.PUT_LINE( 'Result ::' || result);
RETURN result ;
END fn_mac;
/
How about this:
select translate(dbms_random.string('a', 20), 'abcXYZ', '158249') from dual;
or, even MORE random ;)
select translate(dbms_random.string('a', 20), dbms_random.string('a',6), trunc(dbms_random.value(100000,999999))) from dual;
You could start with the Printable option, then strip out any non-alphanumerics:
select SUBSTR(
TRANSLATE(dbms_random.string('p',100)
,'A`~!@#$%^&*()-=_+[]\{}|;'':",./<>?'
,'A')
,1,10) from dual;
(Note: very rarely, this will return less than 10 characters)
or, map the offending characters to other letters and numbers (although this would reduce the randomness quite a bit):
select TRANSLATE(dbms_random.string('p',10)
,'A`~!@#$%^&*()-=_+[]\{}|;'':",./<>? '
,'A' || dbms_random.string('x',33)) from dual;
Great question, by the way.
Now, for my bonus points:
The reason Oracle didn't implement this is because no-one asked for it, and it probably is not a high priority.
create or replace procedure r1
is
v_1 varchar2(1);
v_2 varchar2(10);
begin
for inner_c in 1..10
loop
select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',mod(abs(dbms_random.random), 62)+1, 1) into v_1 from dual;
v_2 := v_2 || v_1;
end loop;
dbms_output.put_line(v_2);
end;
/
