Replace the records with ******** partially in DB2-CodePudding

I want to replace my column with name (Accountno) partially as below using SQL in DB2 IBM, Also I dont want to create a new column need to make changes in the same column

Before Accountno:

234567734
987652309

After Accountno:

23*****34
98*****09

CodePudding user response：

update table
set accoountno =
       substr(accoountno, 1, 2) || '*****' ||
       substr(accoountno, char_length(accoountno) - 1, 2);

CodePudding user response：

You can use Dynamic Data Masking

 Alter table Account Column Accountno MASKED WITH (FUNCTION=’partial(2, ”XXXXXX”,2)’)

CodePudding user response：

You can copy the first and last two characters of account number. Then replace the middle characters with "*" by repeating it 5 times.

 UPDATE test_table
 SET    Accountno = SUBSTR(Accountno, 1, 2) 
        || REPEAT('*', LENGTH(Accountno) - 4) 
        || SUBSTR(Accountno, LENGTH(Accountno) - 1, 2)