在Oracle中生成大小写字母数字随机字符串 [英] Generate Upper and Lowercase Alphanumeric Random String in Oracle

问题描述

如何从 oracle 生成大小写字母数字随机字符串?

How does one generate an upper and lowercase alphanumeric random string from oracle?

我已经使用 select DBMS_RANDOM.STRING('x', 10) from dual 生成大写字母数字字符

I have used select DBMS_RANDOM.STRING('x', 10) from dual to generate uppercase alphanumeric characters

和select DBMS_RANDOM.STRING('a', 10) from dual 生成大小写字母字符

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.

此外，如果您能想到 Oracle 没有实施此功能的充分理由，则可以加分(或只是点赞)?

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

您可能需要调整 0.5 以考虑不同的池大小 - l 为 26，而 x 为 36.(.419354839?).您也可以使用 value() 并传入字符值的开始和结束范围，但这将是特定于字符集的.

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.

至于为什么……甲骨文需要理​​由吗?x 的使用可能表明它最初是十六进制的，并被扩展为包括所有大写字母，而他们没有想到同时添加混合大小写版本.

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.

这篇关于在Oracle中生成大小写字母数字随机字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！