MASK_LAST_N
Description
The MASK_LAST_N function is used to mask the last N bytes of a string. It replaces uppercase letters with X, lowercase letters with x, and digits with n in the last N bytes.
Syntax
MASK_LAST_N(<str>[, <n>])
Parameters
| Parameter | Description |
|---|---|
<str> | The string to be masked. Type: VARCHAR |
<n> | The number of last N bytes to mask (optional, defaults to entire string). Type: INT |
Return Value
Returns VARCHAR type, the string with the last 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 last 4 bytes
SELECT mask_last_n('1234-5678', 4);
+-----------------------------+
| mask_last_n('1234-5678', 4) |
+-----------------------------+
| 1234-nnnn |
+-----------------------------+
- Without specifying n (masks entire string)
SELECT mask_last_n('abc123');
+----------------------+
| mask_last_n('abc123') |
+----------------------+
| xxxnnn |
+----------------------+
- n exceeds string length
SELECT mask_last_n('Hello', 100);
+---------------------------+
| mask_last_n('Hello', 100) |
+---------------------------+
| Xxxxx |
+---------------------------+
- NULL value handling
SELECT mask_last_n(NULL, 5);
+----------------------+
| mask_last_n(NULL, 5) |
+----------------------+
| NULL |
+----------------------+
- n is 0 (masks no characters)
SELECT mask_last_n('Hello123', 0);
+----------------------------+
| mask_last_n('Hello123', 0) |
+----------------------------+
| Hello123 |
+----------------------------+
- n greater than string length (masks entire string)
SELECT mask_last_n('Test', 100);
+--------------------------+
| mask_last_n('Test', 100) |
+--------------------------+
| Xxxx |
+--------------------------+
- Mask email domain part
SELECT mask_last_n('user@example.com', 11);
+-------------------------------------+
| mask_last_n('user@example.com', 11) |
+-------------------------------------+
| user@xxxxxxx.xxx |
+-------------------------------------+
- Mask last 4 digits of phone number
SELECT mask_last_n('13812345678', 4);
+-------------------------------+
| mask_last_n('13812345678', 4) |
+-------------------------------+
| 1381234nnnn |
+-------------------------------+
- Mixed letters, digits and special characters
SELECT mask_last_n('ABC-123-xyz', 7);
+--------------------------------+
| mask_last_n('ABC-123-xyz', 7) |
+--------------------------------+
| ABC-nnn-xxx |
+--------------------------------+
- UTF-8 character handling (masks by byte)
SELECT mask_last_n('Helloṭṛ123', 9);
+--------------------------------+
| mask_last_n('Hello你好123', 9) |
+--------------------------------+
| Hello你好nnn |
+--------------------------------+
SELECT mask_last_n('eeeéèêëìí1234');
+------------------------------------+
| mask_last_n('eeeéèêëìí1234') |
+------------------------------------+
| xxxéèêëìínnnn |
+------------------------------------+
