Friday, January 11, 2019

Mask data in a table column

Below function helps to mask data in a Table column containing numbers and characters. Special characters will be kept same including the position.

CREATE OR REPLACE function datamask_func(word in varchar2)
return varchar2
as
mask_data varchar2(4000);
begin
select translate(word,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',dbms_random.string('U',26)||dbms_random.string('L',26)||replace(dbms_random.random,'-','1')) into mask_data from dual;
  --  dbms_output.put_line('mask_data: '||mask_data);
return mask_data;
end;
/

I used that function to mask data in one of the column like below.

update mask_data_tbl set COL11=datamask_func(COL11);


Hope this helps.