MASK_FIRST_N
Description
The MASK_FIRST_N function is used to mask the first N bytes of a string. It replaces uppercase letters with X, lowercase letters with x, and digits with n in the first N bytes.
Syntax
MASK_FIRST_N(<str>[, <n>])
Parameters
| Parameter | Description |
|---|---|
<str> | The string to be masked. Type: VARCHAR |
<n> | The number of first N bytes to mask (optional, defaults to entire string). Type: INT |
Return Value
Returns VARCHAR type, the string with the first N bytes masked.
Special cases:
- If any parameter is NULL, returns NULL
- Non-alphanumeric characters remain unchanged
- If
<n>is greater than string length, masks the entire string
Examples
- Basic usage: mask first 4 bytes
SELECT mask_first_n('1234-5678', 4);
+------------------------------+
| mask_first_n('1234-5678', 4) |
+------------------------------+
| nnnn-5678 |
+------------------------------+
- Without specifying n (masks entire string)
SELECT mask_first_n('abc123');
+-----------------------+
| mask_first_n('abc123') |
+-----------------------+
| xxxnnn |
+-----------------------+
- n exceeds string length
SELECT mask_first_n('Hello', 100);
+----------------------------+
| mask_first_n('Hello', 100) |
+----------------------------+
| Xxxxx |
+----------------------------+
- NULL value handling
SELECT mask_first_n(NULL, 5);
+-----------------------+
| mask_first_n(NULL, 5) |
+-----------------------+
| NULL |
+-----------------------+
- n is 0 (masks no characters)
SELECT mask_first_n('Hello123', 0);
+-----------------------------+
| mask_first_n('Hello123', 0) |
+-----------------------------+
| Hello123 |
+-----------------------------+
- n greater than string length (masks entire string)
SELECT mask_first_n('Test', 100);
+---------------------------+
| mask_first_n('Test', 100) |
+---------------------------+
| Xxxx |
+---------------------------+
- Mask email address prefix
SELECT mask_first_n('user@example.com', 6);
+-------------------------------------+
| mask_first_n('user@example.com', 6) |
+-------------------------------------+
| xxxx@xxample.com |
+-------------------------------------+
- Mask first 3 digits of phone number
SELECT mask_first_n('13812345678', 3);
+--------------------------------+
| mask_first_n('13812345678', 3) |
+--------------------------------+
| nnn12345678 |
+--------------------------------+
- Mixed letters, digits and special characters
SELECT mask_first_n('Abc-123-XYZ', 7);
+---------------------------------+
| mask_first_n('Abc-123-XYZ', 7) |
+---------------------------------+
| Xxx-nnn-XYZ |
+---------------------------------+
- UTF-8 character handling (masks by byte)
SELECT mask_first_n('ṭṛWorld123', 7);
+-----------------------------------+
| mask_first_n('ṭṛWorld123', 7) |
+-----------------------------------+
| ṭṛXorld123 |
+-----------------------------------+
SELECT mask_first_n('eeeéèêëìí1234');
+-------------------------------------+
| mask_first_n('eeeéèêëìí1234') |
+-------------------------------------+
| xxxéèêëìínnnn |
+-------------------------------------+
