Obscuring data in database

At times, we may share database for development purpose. And we may need to obscure certain data. One example may be to obscure all email address in a particular table. Following sql statement obscures the local part of email address (before @ sign) and prefixes the domain name with two underscores (__).

update  Employeee_Table emp
set emp.email_id = concat(Translate(SUBSTR(emp.email_id, 1,  Instr(emp.email_id, '@')-1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '84cBF75ZKnbEHofTQRSs6etN9zrW03yUmIkvxVijwJOdl1uAhMqCD2pGLXgaPY'), replace((SUBSTR(emp.email_id, Instr(emp.email_id, '@'))),'@','@__'))

Translate function, available in both - IBM DB2 and Oracle, changes character by character. So, in above query all 'A's will be changed '8', 'B's to 4, 'C's to 'c', 'D's to 'B' and so on. Replace function replaces '@' with '@__'

You can randomize string for your use at http://textmechanic.com/String-Randomizer.html

Obscuring domain name would have made key more predictable. So, either we should choose to use different keys for local part and domain name or just obscure local part.

Prefixing '_' with domain name helps in avoiding any accidental email to be sent to correct domain name.

Also, this isn't any encryption and should not be used where security is important. This is just to obscure data.