Usage of TRANSLATE and DBMS_RANDOM to anonymise numbers

You may come across a situation where a developer or designer needs some referential data from a data but it would be inappropriate to send them the real information. Here’s where the TRANSLATE and DBMS_RANDOM packages can help.

TRANSLATE changes a character to another one chosen from a string. It matches in order so if the first character matches then it will substitute it with the first character from the given string.

DBMS_RANDOM can generate random numbers.

Using these in combinations allows you to substitute a number with a randomly generated one.

Here’s an example to randomise a credit card number:

select translate(CARD_NUMBER,'0123456789',round(dbms_random.value(0000000000000000,99999999999999999)) as HIDDEN_CARD_NUMBER from CARDS;


And one for IP addresses, the value here is that the context remains so the field lengths are maintained as are the . separators:

select translate(IP_ADDRESS,'0123456789',round(dbms_random.value(000000000000,9999999999999))) as HIDDEN_IP_ADDRESS   from IPS;


Playing around with these gives a powerful tool to avoid exposing your data to unauthorised parties whilst allowing them to see enough of the data context to do their job. Hope this is useful.

Leave a Reply