Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Marcin.

Asked: June 18, 2018 - 7:42 am UTC

Last updated: June 18, 2018 - 11:27 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

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

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library