MySQL ‐ Data Masking Function - shaysalomon12/Data-Engineer GitHub Wiki

Create Database to hold the function with UTF8 CHARACTER SET and Collaction

CREATE DATABASE dba CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create the fnRandomizedText Function

USE dba;

DROP FUNCTION IF EXISTS fnRandomizedText;

DELIMITER $$
CREATE FUNCTION fnRandomizedText (
OldValue varchar(255)
)
RETURNS char(255)
DETERMINISTIC 
BEGIN

  DECLARE NewValue VARCHAR(255);
  DECLARE nCount INT;
  DECLARE cCurrent CHAR(1);
  DECLARE cScrambled CHAR(1);
  DECLARE Random REAL;
 
  SET OldValue = REPLACE(OldValue, ' ','&');
  SET NewValue = '';
  SET nCount = 0;

WHILE (nCount <= length(OldValue)) DO

	SET Random = RAND();
        SET cCurrent = SUBSTRING(OldValue, nCount, 1);

	-- English lowercase
	IF ASCII(cCurrent) BETWEEN ASCII('a') AND ASCII('z') THEN
                SET cScrambled = CHAR(ROUND(((ASCII('z') - ASCII('a') - 1) * RAND() + ASCII('a')), 0) USING UTF8MB4);
	       
	-- English uppercase
	ELSEIF ASCII(cCurrent) BETWEEN ASCII('A') AND ASCII('Z') THEN
		SET cScrambled = CHAR(ROUND(((ASCII('Z') - ASCII('A') - 1) * RAND() + ASCII('A')), 0) USING UTF8MB4);
    
	-- Hebrew (Convert to Hexa and than to Decimal)
	ELSEIF conv(hex(cCurrent),16,10) BETWEEN conv(hex('א'),16,10) AND conv(hex('ת'),16,10) THEN
		SET cScrambled = CHAR(ROUND(((conv(hex('ת'),16,10) - conv(hex('א'),16,10) - 1) * RAND() + conv(hex('א'),16,10)), 0) USING UTF8MB4);
	
	-- Numbers
	ELSEIF ASCII(cCurrent) BETWEEN ASCII('0') AND ASCII('9') THEN
		SET cScrambled = CHAR(ROUND(((ASCII('9') - ASCII('0') - 1) * RAND() + ASCII('0')), 0) USING UTF8MB4);
	
	-- Arabic
	ELSEIF ASCII(cCurrent) BETWEEN 1548 AND 1746 THEN
		SET cScrambled = CHAR(ROUND(((1746 - 1548 - 1) * RAND() + 1548), 0) USING UTF8MB4);
	
        ELSE
                SET cScrambled = CONVERT(cCurrent USING UTF8MB4);
       
        END IF;
	
        SET NewValue = concat(NewValue,cScrambled);
	SET nCount = nCount + 1;
    
END WHILE;
	
    -- Return original space
    RETURN LTRIM(RTRIM(REPLACE(NewValue,'&',' ')));

END
$$
DELIMITER ;

Run Tests

select dba.fnRandomizedText('Sharon Adams');
select dba.fnRandomizedText('[email protected]');
select dba.fnRandomizedText('Arik Kuperman');
select dba.fnRandomizedText('אריק ברמן');	
select dba.fnRandomizedText('024511278');
select dba.fnRandomizedText('4580-3211-5210-3120');