oracle 在Oracle中生成大小写字母数字随机字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5549781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate Upper and Lowercase Alphanumeric Random String in Oracle
提问by colinjwebb
How does one generate an upper and lowercase alphanumeric random string from oracle?
如何从 oracle 生成大小写字母数字随机字符串？
I have used select DBMS_RANDOM.STRING('x', 10) from dualto generate uppercase alphanumeric characters
我曾经用来select DBMS_RANDOM.STRING('x', 10) from dual生成大写字母数字字符
and select DBMS_RANDOM.STRING('a', 10) from dualto generate uppercase and lowercase alpha characters
并select DBMS_RANDOM.STRING('a', 10) from dual生成大写和小写字母字符
...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?
另外，如果您能想到 Oracle 没有实施此功能的充分理由，则可以加分（或只是点赞）？
采纳答案by Alex Poole
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.5to take into account the different pool sizes - 26 for lvs. 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.
您可能需要调整0.5以考虑不同的池大小 - 26l与 36 x。( .419354839?). 您还可以使用 value() 并传入字符值的开始和结束范围，但这将是特定于字符集的。
As to why... do Oracle need a reason? The use of xmight 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.
至于为什么……甲骨文需要理由吗？使用xmay 表明它最初是十六进制的，并扩展为包括所有大写字母，而他们没有想到同时添加混合大小写版本。
回答by Janek Bogucki
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
回答by harsha
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;
/
回答by tbone
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;
回答by Jeffrey Kemp
You could start with the Printable option, then strip out any non-alphanumerics:
您可以从 Printable 选项开始，然后去掉任何非字母数字：
select SUBSTR(
TRANSLATE(dbms_random.string('p',100)
,'A`~!@#$%^&*()-=_+[]\{}|;'':",./<>?'
,'A')
,1,10) from dual;
(Note: very rarely, this will return less than 10 characters)
（注意：很少，这将返回少于 10 个字符）
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.
Oracle 没有实现这一点的原因是没有人要求它，而且它可能不是一个高优先级。
回答by vinod
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;
/
