SELECT statement that will mask a value stored on a DB2 table
Posted on 2008-10-31
I have a situation where I want to mask an employeeID column in a database (DB2). I want the data stored on the database to remain 'in-the-clear', but I want to be able to create a SELECT statement that grabs the masked value, rather than the full string. The masking rules are as follows:
1. If the employeeID is less than 10 digits, then pad field before scrubbing.
2. If the account number is 7 or more digits scrub to the last 4 digits of the bank account number, preceded by lower case xxx to the required length.
3. If the account number is 6 digits or less scrub to the last two, preceded by lower case xxx to the required length.
Examples of the data stored on the DB, and desired values from the SELECT statement are included below:
Length employeeID (stored on DB) employeeID (desired value)
1 1 xxxxxxxxx1
2 12 xxxxxxxx12
3 123 xxxxxxxx23
4 1234 xxxxxxxx34
5 12345 xxxxxxxx45
6 123456 xxxxxxxx56
7 1234567 xxxxxx4567
8 12345678 xxxxxx5678
9 123456789 xxxxxx6789
10 1234567890 xxxxxx7890
11 12345678901 xxxxxxx8901
12 123456789012 xxxxxxxx9012
13 1234567890123 xxxxxxxxx0123
14 12345678901234 xxxxxxxxxx1234
15 123456789012345 xxxxxxxxxxx2345
Any help that could point me in the right direction would be much appreciated! Many thanks!!!