Solved

SELECT statement that will mask a value stored on a DB2 table

Posted on 2008-10-31
5
1,087 Views
Last Modified: 2012-06-22
Hi All,

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!!!

-Mike
0
Comment
Question by:morciani
  • 2
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 22852604
Hi morciani,

Do you want to SELECT based on these rules (which may well result in multiple rows returned for a querty) or just mask the returned results so that the client side doesn't see the entire value?

What is the data type involved?  The function will be similar regardless of the type, but an entirely different set of functions will be needed.


Kent
0
 

Author Comment

by:morciani
ID: 22853346
Hello- I would like the SELECT to perform the masking, so the masked value is returned to the client.  I would expect that the query returns multiple rows, based on parameters that I pass into the WHERE clause.  The datatype of the employeeID is CHAR(20).  Thank you so much for the help!
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 22853531
It appears that all of the employee ID values are stored left justified, if so try this:

Note that if employeeID is null or blank, this will need a bit more code.  Also, a value of 20 digits is an issue.

Kent

SELECT

  case when posstr (employeeID, ' ') = 2 then left ('xxxxxxxxx' || employeeID, 10)

       when posstr (employeeID, ' ') < 8 then 

            left ('xxxxxxxx' || substr (employeeID, posstr (employeeID, ' ') - 2, 2), 10)

       when posstr (emloyeeID, ' ') < 11 then

            left ('xxxxxx' || substr (employeeID, posstr (employeeID, ' ') - 4, 4), 10)

       else substr ('xxxxxxxxxxxxxxxxxxxx', 1, posstr (employeeID, ' ') - 5) ||

                   substr (employeeID, posstr (employeeID, ' ') - 4, 4)

  end

FROM ...

Open in new window

0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22860032
please ignore the WITH it was just for my testing..

what I really don't understand, why do numbers of length 6 or less only keep the last 2 digits but 7 or greater keep 4 digits?

do you want a function that always returns 10 or more charaters?

with my_table as (

select '12345678901234567890' my_column from sysibm.sysdummy1 union all

select '1234567890123456789' my_column from sysibm.sysdummy1 union all

select '123456789012345678' my_column from sysibm.sysdummy1 union all                            

select '12345678901234567' my_column from sysibm.sysdummy1 union all

select '1234567890123456' my_column from sysibm.sysdummy1 union all

select '123456789012345' my_column from sysibm.sysdummy1 union all

select '12345678901234' my_column from sysibm.sysdummy1 union all

select '1234567890123' my_column from sysibm.sysdummy1 union all

select '123456789012' my_column from sysibm.sysdummy1 union all

select '12345678901' my_column from sysibm.sysdummy1 union all

select '1234567890' my_column from sysibm.sysdummy1 union all

select '123456789' my_column from sysibm.sysdummy1 union all

select '12345678' my_column from sysibm.sysdummy1 union all

select '12345678' my_column from sysibm.sysdummy1 union all

select '1234567' my_column from sysibm.sysdummy1 union all

select '123456' my_column from sysibm.sysdummy1 union all

select '12345' my_column from sysibm.sysdummy1 union all

select '1234' my_column from sysibm.sysdummy1 union all

select '123' my_column from sysibm.sysdummy1 union all

select '12' my_column from sysibm.sysdummy1 union all

select '1' my_column from sysibm.sysdummy1)

select  my_column, length(my_column) my_column_length,

   case when length(my_column) < 7

    then right(repeat('x',20) || rtrim(ltrim(right(my_column,2))) ,10)

    when length(my_column) < 11

    then right(repeat('x',20) || right(my_column,4),10)

    else right(repeat('x',20) || right(my_column,4),length(my_column))

    end padded_column

from my_table

order by my_column

;

Open in new window

0
 

Author Closing Comment

by:morciani
ID: 31512113
This is EXACTLY what I needed!  It functions exactly as I need it to.  Many thanks!!!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now