how to randomize column data and mask data using mysql-CodePudding

DISCLAIMER: NONE OF THESE VALUES ARE TRUE/REAL, ITS JUST A PRACTICE ASSIGNMENT

how to randomize the last 6 digits of the DBS account and obscuring the first 4 digits of the NRIC number with x using mysql. All values were keyed in manually and do not relate to each other.

Current

Desired Result

CodePudding user response：

Just use substring operations:

SELECT
    CONCAT('XXXX', SUBSTRING(NRIC, 5, 4)) AS NRIC,
    Name,
    Contact,
    Salary,
    CONCAT(SUBSTRING(DBS_Account, 1, 3), '-XXXXX-X') AS DBS_Account
FROM yourTable;

CodePudding user response：

mysql rand function - https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round

using substring and concat

SELECT CONCAT(SUBSTRING(RAND(),3,5),'-',SUBSTRING(RAND(),3,1))

will give something like 40033-8

then concat with substring

set @a ='038-12645-6';
SELECT CONCAT(substring_index(@a,'-',1),'-',SUBSTRING(RAND(),3,5),'-',SUBSTRING(RAND(),3,1));

'038-63475-5'