Question 20388 in Mysql Question posted in
The official MySQL documentation can be found here.

Partialy Hiding PII in a View – Mysql

I have some columns with PII that I need to share with a coworker. They need to be able to see 50% of the characters and the other 50% should be replaced by Asterisks, but I dont know how to do it.

Also, would you use a table or a view to do this? Do you have any advice? I’m pretty new in SQL.

So far, my code is something like this:


CREATE TABLE clients (
client_id INT NOT NULL,
mail VARCHAR(30) NOT NULL,
phone INT NOT NULL,
PRIMARY KEY (client_id)
);

INSERT INTO clients VALUES 
(1,'[email protected]',23234123),
(2,'[email protected]',23235543),
(3,'[email protected]',343435523);

CREATE VIEW clients_partial as (
SELECT client_id,
mail as mail_partial, #### PROBLEM TO SOLVE  
phone as phone_partial ### PROBLEM TO SOLVE
FROM clients);

SELECT * FROM clients_partial

What I have in clients_partial:
enter image description here

What I need in clients_partial:

enter image description here

Tags:

2

Answers


  1. 0

    Determine length of string
    Extract first xx characters
    Pad that with asterisks to the length of the string

     // show first xx chars of string padded with asterisk to length of string
 $str = "Hello World this is a versy long string that is very long";
 $strlength = strlen($str); // get the length of the string
 $str = substr($str, 0,10); // shorten it to 10 characters starting from beginning
 $newstr =  str_pad($str,$strlength,"*"); // show first 10 characters, then * until length of original string

    Adjust as needed.

    Login or Signup to reply.
  2. 0

    It is not completely identical, but to anonymize it should be good

    CREATE TABLE clients (
client_id INT NOT NULL,
mail VARCHAR(30) NOT NULL,
phone INT NOT NULL,
PRIMARY KEY (client_id)
);

INSERT INTO clients VALUES 
(1,'[email protected]',23234123),
(2,'[email protected]',23235543),
(3,'[email protected]',343435523);

CREATE VIEW clients_partial as (
SELECT client_id
  ,
CONCAT(
concat(SUBSTRING_INDEX(mail,'@', 1),'@'
  ,LEFT(
  SUBSTRING_INDEX(mail,'@', -1)
  ,1))
  ,  CONCAT(LEFT(
  Right(SUBSTRING_INDEX(mail,'@', -1),length(SUBSTRING_INDEX(mail,'@', -1))  -1)
  ,-length(SUBSTRING_INDEX(mail,'@', -1))  -1),
  REPEAT('*', 
  length(
  Right(
  SUBSTRING_INDEX(mail,'@', -1)
  ,length(SUBSTRING_INDEX(mail,'@', -1))  -1
  )
  ) -1))
  ) as mail_partial, #### PROBLEM TO SOLVE  
concat(left(phone,length(phone) -4),'****') as phone_partial ### PROBLEM TO SOLVE
FROM clients
  );

SELECT * FROM clients_partial


    Records: 3  Duplicates: 0  Warnings: 0
    client_id mail_partial phone_partial
    1 player_1@g******* 2323****
    2 player_2@o********* 2323****
    3 player_3@h********* 34343****

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search