CODEWITHSUNDEEP
mysql
Murat
Murat

Reputation: 55

Mask or replace column values mysql any column data

How can we select any column values and mask them with a default value;

Example table called pet:

+--------+-------+-----------+
|  name  | owner | species   |
+--------+-------+-----------+
| Elvis  | Tom   | Dog       |
| Harley | Mary  | Cat       |
+--------+-------+-----------+

We want to mask name column with 'MASKED'. I used this select query but is there a better way?

select 
   case name
     when 'Elvis' then 'MASKED'
     else 'MASKED'
   end as name
 from mysql.pet;

Result: 

+-------------------------------+
  name              |
+-------------------------------+
| MASKED                        |
| MASKED                        |
+-------------------------------+

Upvotes: 0

Views: 1108

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43584

You can use the following to mask all values on column name with "Masked":

SELECT 'MASKED' AS name, owner, species 
FROM pet

If you only want to mask a specific name (e.g. "Elvis") you can use the following (using CASE):

SELECT CASE WHEN name = 'Elvis' THEN 'MASKED' ELSE name END AS name, owner, species 
FROM pet

You can also replace the value with a specific char (like *):

SELECT LPAD('', LENGTH(name), '*') AS name, owner, species
FROM pet

demo on dbfiddle.uk

Upvotes: 2

Related Questions