Generate Upper and Lowercase Alphanumeric Random String in Oracle
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 Ora开发者_开发问答cle 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
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;
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;
/
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;
/
精彩评论