| Masking of Customer Data [message #623417] |
Wed, 10 September 2014 04:43  |
 |
tigsav
Messages: 49
Registered: April 2012
|
Member |
|
|
Hi,
I have a data in a table which contains customer sensitive information.
create table test_10092014
(
REFERENCE_NO VARCHAR2(16),
TEXT VARCHAR2(255)
)
insert into test_10092014
values ('1016265132480457', 'CHG AND VAT IPO REFUND');
insert into test_10092014
values ('1016263132480457','IPO REFUND BANGLADESH BUILDING SYSTEMS LTD');
insert into test_10092014
values ('001TEXT13251S4NM', '4377405109414860 ATM01081 325116360435');
insert into test_10092014
values ('001TEXT13251S0JI', '4377405109414860 ATM01081 324919028987');
insert into test_10092014
values ('001TEXT13272T5EV', '437740******486* ATM01081 327119481030');
insert into test_10092014
values ('001TEXT13273T5X8', '437740******486* ATM00125 327308497199');
commit;
Now from the data in the table ,for reference_no having TEXT in them have Card number in their corresponding text column.
This data that needs to be masked in the format 437740******486*
Please Help with this .
Can this be done by sql query.
Regards,
tigsav
|
|
|
|
| Re: Masking of Customer Data [message #623422 is a reply to message #623417] |
Wed, 10 September 2014 05:04  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Good old SUBSTR does the job.
SQL> select reference_no, text,
2 case when instr(reference_no, 'TEXT') > 0 then
3 substr(text, 1, 6) || '******' ||substr(text, 13, 3) || '*' || substr(text, 17, length(text))
4 else text
5 end text_new
6 from test;
REFERENCE_NO TEXT TEXT_NEW
---------------- ------------------------------------------ ------------------------------------------
1016265132480457 CHG AND VAT IPO REFUND CHG AND VAT IPO REFUND
1016263132480457 IPO REFUND BANGLADESH BUILDING SYSTEMS LTD IPO REFUND BANGLADESH BUILDING SYSTEMS LTD
001TEXT13251S4NM 4377405109414860 ATM01081 325116360435 437740******486* ATM01081 325116360435
001TEXT13251S0JI 4377405109414860 ATM01081 324919028987 437740******486* ATM01081 324919028987
001TEXT13272T5EV 437740******486* ATM01081 327119481030 437740******486* ATM01081 327119481030
001TEXT13273T5X8 437740******486* ATM00125 327308497199 437740******486* ATM00125 327308497199
6 rows selected.
SQL> |
|
|
|
| Re: Masking of Customer Data [message #623423 is a reply to message #623417] |
Wed, 10 September 2014 05:05 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SQL> WITH data AS
2 ( SELECT * FROM TEST_10092014
3 )
4 SELECT REFERENCE_NO,
5 SUBSTR(TEXT,1,6)
6 ||'******'
7 ||SUBSTR(TEXT, 13,3)
8 ||'*'
9 ||SUBSTR(TEXT,17) str
10 FROM DATA
11 WHERE REFERENCE_NO LIKE '%TEXT%'
12 /
REFERENCE_NO STR
---------------- ---------------------------------------------
001TEXT13251S4NM 437740******486* ATM01081 325116360435
001TEXT13251S0JI 437740******486* ATM01081 324919028987
001TEXT13272T5EV 437740******486* ATM01081 327119481030
001TEXT13273T5X8 437740******486* ATM00125 327308497199
Regards,
Lalit
|
|
|
|
|
|
|
|
|
|
| Re: Masking of Customer Data [message #623453 is a reply to message #623441] |
Wed, 10 September 2014 06:08 |
|
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
tigsav wrote on Wed, 10 September 2014 12:44
I had put an awful lot of Pl/sql into doing this.
Could you, by any chance, post that PL/SQL code? It might be interesting to compare what you managed to do with what has been suggested here.
[Updated on: Wed, 10 September 2014 06:08] Report message to a moderator |
|
|
|
|
|
| Re: Masking of Customer Data [message #623504 is a reply to message #623453] |
Wed, 10 September 2014 22:39 |
|
tigsav
Messages: 49
Registered: April 2012
|
Member |
|
|
This is what i had did before you had given me the simplest form to do it .
declare
cursor c_patch_recs IS
select reference_no, text
from test_10092014 a
where a.reference_no LIKE '%TEXT%'
AND a.text NOT LIKE '%*%';
v_rec c_patch_recs%ROWTYPE;
v_pan VARCHAR2(255);
v_term_id VARCHAR2(255);
v_rrn VARCHAR2(255);
v_end_locn NUMBER;
v_start_locn NUMBER := 1;
v_masked_pan VARCHAR2(255);
v_masked_text VARCHAR2(255);
begin
FOR v_rec IN c_patch_recs LOOP
v_start_locn := 1;
v_end_locn := 1;
v_end_locn := instr(v_rec.text, ' ', v_start_locn, 1);
v_pan := substr(v_rec.text,
v_start_locn,
v_end_locn - v_start_locn);
v_start_locn := v_end_locn + 2;
v_end_locn := instr(v_rec.text, ' ', v_start_locn, 1);
v_term_id := substr(v_rec.text,
v_start_locn,
v_end_locn - v_start_locn);
v_start_locn := v_end_locn + 2;
v_rrn := substr(v_rec.text,
v_start_locn,
length(v_rec.text) - v_start_locn);
EXIT WHEN c_patch_recs%NOTFOUND;
v_masked_pan := SUBSTR(v_pan, 1, 6) || '******' || substr(v_pan, 13, 3) || '*';
v_masked_text := v_masked_pan || ' ' || v_term_id || ' ' || v_rrn;
dbms_output.put_line('v_masked_text::' || v_masked_text);
END LOOP;
end;
Lalit : Removed superfluous lines from end of code.
[Updated on: Thu, 11 September 2014 00:20] by Moderator Report message to a moderator |
|
|
|
| Re: Masking of Customer Data [message #623507 is a reply to message #623504] |
Thu, 11 September 2014 00:19 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
tigsav wrote on Thu, 11 September 2014 09:09
v_masked_pan := SUBSTR(v_pan, 1, 6) || '******' || substr(v_pan, 13, 3) || '*';
Quite close. You just had to do it in SQL with SUBSTR(TEXT,17) concatenated to it.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
