I have a table data with combination of Integer and strings. Like this

CREATE TABLE EMPLOYEE (
  empId INTEGER,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL
);


INSERT INTO EMPLOYEE VALUES (12345, 'Clark Duff', '[email protected]',9001234567);
INSERT INTO EMPLOYEE VALUES (22245, 'Dave Johnson', '[email protected]',9000123456);
INSERT INTO EMPLOYEE VALUES (55456, 'Ava evelene', '[email protected]',9000012345);

But I'm looking for a common function in MYSQL to check the condition whether it is a special character or integer or string. Below syntax will mask the data:

If it is an integer then this condition needs to execute:

concat(SUBSTRING(phone,1,3) , '*****' , SUBSTRING(phone,7,4)) phone

If it is a special character (@) then this condition needs to execute:

CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`Mail`, '@', -1)) as Mail

ELSE string then other script

CodePudding user response：

seem you are looking for case statement applied to like condition for @ char or cast to usingned for chekc a valid integer number

select case 
  when email like '%@%' then CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`Mail`, '@', -1)) 
  when cast(phone  AS UNSIGNED) != 0 THEN concat(SUBSTRING(phone,1,3) , '*****' , SUBSTRING(phone,7,4))
  else 'not managed' 
end

CodePudding user response：

Here is an example stored function. Is this what you mean?

CREATE FUNCTION `someFunc`(
    input VARCHAR(255)
)
RETURNS VARCHAR(255)
NOT DETERMINISTIC
BEGIN
    IF input LIKE '%@%' THEN
        RETURN CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(input, '@', -1));
    ELSEIF input REGEXP '^[0-9 ]{7,10}$' THEN
        RETURN CONCAT(SUBSTRING(input, 1, 3) , '*****' , SUBSTRING(input, 7, 4));
    ELSE
        RETURN input;
    END IF;
END

You may want to use LEFT(MD5(input), 8) instead of LEFT(UUID(), 8) so that the function can be declared as deterministic.

CREATE FUNCTION `someFunc`(
    input VARCHAR(255)
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    IF input LIKE '%@%' THEN
        RETURN CONCAT(LEFT(MD5(input), 8), '@', SUBSTRING_INDEX(input, '@', -1));
    ELSEIF input REGEXP '^[0-9 ]{7,10}$' THEN
        RETURN CONCAT(SUBSTRING(input, 1, 3) , '*****' , SUBSTRING(input, 7, 4));
    ELSE
        RETURN input;
    END IF;
END

WITH `EMPLOYEE` (`empId`, `name`, `email`, `phone`) AS (
    SELECT 12345, 'Clark Duff', '[email protected]',9001234567 UNION
    SELECT 22245, 'Dave Johnson', '[email protected]',9000123456 UNION
    SELECT 55456, 'Ava evelene', '[email protected]',9000012345
)
SELECT
    empId,
    someFunc(`name`) AS `name`,
    someFunc(`email`) AS `email`,
    someFunc(`phone`) AS `phone`
FROM `EMPLOYEE`;

CodePudding user response：

One UDF to mask them.

CREATE FUNCTION fnMaskUserInfo (
    input TEXT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
    IF input LIKE '%_@_%._%' 
    THEN
        RETURN CONCAT(RIGHT(SHA1(input),8),'@',SUBSTRING_INDEX(input,'@',-1));
    END IF;
    
    IF input REGEXP '^[0-9]{7,10}$' 
    THEN
        RETURN CONCAT(SUBSTRING(input,1,3),'*****',SUBSTRING(input,7,4));
    END IF;
    
    RETURN input;
END

SELECT empId
, fnMaskUserInfo(Name) AS Name
, fnMaskUserInfo(email) AS email
, fnMaskUserInfo(phone) AS phone
FROM EMPLOYEE

Demo on db<>fiddle here