Syntax error How to mask data fields in MySQL?

How to mask data fields in MySQL?

To mask data fields, use CONCAT() along with REPEAT(). Here, we will mask data fields with #. Let us first create a −

mysql> create table DemoTable1410
   -> (
   -> Password varchar(80)
   -> );
Query OK, 0 rows affected (0.51 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1410 values('John12345678');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1410 values('Carol_897');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1410 values('David_5647383');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select −

mysql> select * from DemoTable1410;

This will produce the following output −

+---------------+
| Password      |
+---------------+
| John12345678  |
| Carol_897     |
| David_5647383 |
+---------------+
3 rows in set (0.00 sec)

Here is the query to mask data fields in MySQL −

mysql> update DemoTable1410
   -> set Password=concat(substr(Password, 1, 5), repeat('#', char_length(Password) - 5));
Query OK, 3 rows affected (0.16 sec)
Rows matched: 3  Changed: 3 Warnings: 0

Let us check the table records once again −

mysql> select * from DemoTable1410;

This will produce the following output −

+---------------+
| Password      |
+---------------+
| John1#######  |
| Carol####     |
| David######## |
+---------------+
3 rows in set (0.00 sec)
Updated on: 2019-11-12T05:08:56+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements