Search code examples
sqloracle-databaseoracle12c

Randomize integers in a string

I am attempting to randomize all integers in a string. E.g "Transferred to account 123456789" randomized into "Transferred to account 256829876"

I already have a slow solution in PL/SQL where I am looping through each character in the string individually. If char is an asci value between 48-57 (digits 0 to 9), I randomize the digit accordingly.

In SQL I have gotten this far:

select regexp_replace('Transferred to account 05172262116','[0-9]',
                      floor(dbms_random.value(0, 10)))
from dual;

However, this does not give me the expected result as integers are replaced with a single unique value. (E.g. 'Transferred to account 555555555') Is it possible to achieve what I am looking for via use of SQL? Thanks.

Solution

  • If you know the numbers are always 11 digits, you can explicitly look for that:

    select regexp_replace('Transferred to account 05172262116','[0-9]{11}', floor(dbms_random.value(10000000000, 99999999999)))
from dual;

    Otherwise, you can replace with an integer, but the length may not be the same length as the original one:

    select regexp_replace('Transferred to account 05172262116','[0-9]+', floor(dbms_random.value(10000000000, 99999999999)))
from dual;

    As a note: things like account numbers are often removed using translate(), but this produces a fixed string:

    select translate('Transferred to account 05172262116', ' 0123456789', ' ##########')
from dual;

    (And you can do the same thing with regexp_replace().)