What's the best way to replace every character in string on random character and every number on other random number. I think about best performance.
Input: MatijZ34
Output: sWirpt77
So here's a means of generating a random variation of the standard alphabet, eg
SQL> set serverout on
SQL> declare
2 type rand_list is table of varchar2(62) index by pls_integer;
3 source varchar2(62) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
4 perms rand_list;
5 source_variant varchar2(62);
6 begin
7 for i in 1 .. 1 loop
8 source_variant := null;
9 for j in ( select rownum r from dual connect by level <= 52 order by dbms_random.value )
10 loop
11 source_variant := source_variant || substr(source,j.r,1);
12 end loop;
13 for j in ( select rownum r from dual connect by level <= 10 order by dbms_random.value )
14 loop
15 source_variant := source_variant || substr(source,52+j.r,1);
16 end loop;
17 perms(i) := source_variant;
18 dbms_output.put_line(source_variant);
19 end loop;
20 end;
21 /
vPXGlJNLVUcEChAeFfRpgWmtwodZBiurSsDOyKkQHTnbzYaMIjxq8053421796
So you can see we randomised:
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789
to
vPXGlJNLVUcEChAeFfRpgWmtwodZBiurSsDOyKkQHTnbzYaMIjxq8053421796
The reason that is useful is that we can now do:
translate(some_string,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
'vPXGlJNLVUcEChAeFfRpgWmtwodZBiurSsDOyKkQHTnbzYaMIjxq8053421796')
to randomise a string in the way you want.
If you want to do this to LOTS of strings, then obviously this simple mapping is easy to crack - this might be want to you want, it might not be...You can adjust the 'i' loop above to generate more then one, eg
SQL> declare
2 type rand_list is table of varchar2(62) index by pls_integer;
3 source varchar2(62) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
4 perms rand_list;
5 source_variant varchar2(62);
6 begin
7 for i in 1 .. 10 loop
8 source_variant := null;
9 for j in ( select rownum r from dual connect by level <= 52 order by dbms_random.value )
10 loop
11 source_variant := source_variant || substr(source,j.r,1);
12 end loop;
13 for j in ( select rownum r from dual connect by level <= 10 order by dbms_random.value )
14 loop
15 source_variant := source_variant || substr(source,52+j.r,1);
16 end loop;
17 perms(i) := source_variant;
18 dbms_output.put_line(source_variant);
19 end loop;
20 end;
21 /
WQjEPGAgHVbrCBcJpoRvkaDzMeuSYnqwLOTXfFtZUmdsKINiyhxl1247809653
oxFArOMdVQsBwiqLRmyCnTkvjHJZpPcNYuESlIXezDbhaKgtWUGf8571260943
crRvsbILOjNgqDapJuTQmCVHAzxGhXMeFSyPUdBklZWoYEifwntK1963284570
XcrlwPiJbdupoYagKSEMsBAFkqtOjmHUIQyhRZfNWCDxLTznevGV6524178309
efHdpyPIhXUvbzlntuRAODmWYEZrqiSLFakoCJgVxGBscjTKMNQw2107456893
zANXWVOlemgGsqZQbcIvjLtpKUSwkRyfMDridnEahxPFJTuHYCBo7498056132
FDtSXTgGzpJwxRUnHcvaiQAZqIlYMOBbhWeCofNLksEmyKVdurjP1726903845
gcJjkGFvMXZyHTsVmDneIzohiPYLuQKadEwxBfSRltqUpCOrWAbN6095378412
ONDoReHqkfYMFypXvQnjtVUBrusWCLPEKzwgcxbGIdTSZahiAmJl3174526908
GuTvzdMnfYmaAFUsKrJBlyDQcSVtoqOikbgXNxREIwZPCeHpjWhL5647832091
These are all being stored in an array called 'perms'. So now you can pick one of *these* at random, eg
translate(some_string,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
perms(trunc(dbms_random.value(1,10.99)))
)
So it will perform a random translation with subsequent execution. You could have 100's of such permutations.