MASK
Description
The MASK function is used to mask data to protect sensitive information. The default behavior is to convert uppercase letters to X, lowercase letters to x, and digits to n.
Syntax
MASK(<str>[, <upper>[, <lower>[, <number>]]])
Parameters
| Parameter | Description |
|---|---|
<str> | The string to be masked. Type: VARCHAR |
<upper> | Character to replace uppercase letters with, default is X (optional). Type: VARCHAR |
<lower> | Character to replace lowercase letters with, default is x (optional). Type: VARCHAR |
<number> | Character to replace digits with, default is n (optional). Type: VARCHAR |
Return Value
Returns VARCHAR type, the string with letters and digits replaced.
Special cases:
- If any parameter is NULL, returns NULL
- Non-alphanumeric characters remain unchanged
- If replacement character parameters contain multiple characters, only the first character is used
Examples
- Basic usage: default replacement rules
SELECT mask('abc123XYZ');
+-------------------+
| mask('abc123XYZ') |
+-------------------+
| xxxnnnXXX |
+-------------------+
- Custom replacement characters
SELECT mask('abc123XYZ', '*', '#', '$');
+----------------------------------+
| mask('abc123XYZ', '*', '#', '$') |
+----------------------------------+
| ###$$$*** |
+----------------------------------+
- Special characters remain unchanged
SELECT mask('Hello-123!');
+--------------------+
| mask('Hello-123!') |
+--------------------+
| Xxxxx-nnn! |
+--------------------+
- NULL value handling
SELECT mask(NULL);
+------------+
| mask(NULL) |
+------------+
| NULL |
+------------+
- Digits-only string
SELECT mask('1234567890');
+--------------------+
| mask('1234567890') |
+--------------------+
| nnnnnnnnnn |
+--------------------+
- Letters-only string
SELECT mask('AbCdEfGh');
+------------------+
| mask('AbCdEfGh') |
+------------------+
| XxXxXxXx |
+------------------+
- Empty string handling
SELECT mask('');
+----------+
| mask('') |
+----------+
| |
+----------+
- Single character replacement (takes first character from multiple)
SELECT mask('Test123', 'ABC', 'xyz', '999');
+--------------------------------------+
| mask('Test123', 'ABC', 'xyz', '999') |
+--------------------------------------+
| Xxxx999 |
+--------------------------------------+
- Mask credit card number
SELECT mask('1234-5678-9012-3456');
+-----------------------------+
| mask('1234-5678-9012-3456') |
+-----------------------------+
| nnnn-nnnn-nnnn-nnnn |
+-----------------------------+
- Mask email address
SELECT mask('user@example.com');
+--------------------------+
| mask('user@example.com') |
+--------------------------+
| xxxx@xxxxxxx.xxx |
+--------------------------+
SELECT mask('eeeéèêëìí1234');
+-----------------------------+
| mask('eeeéèêëìí1234') |
+-----------------------------+
| xxxéèêëìínnnn |
+-----------------------------+
