Masking data in Oracle | Example Mobile number / Aadhaar Number

🔹 Masking a Mobile Number in Oracle

Masking a mobile number means partially hiding digits for privacy while displaying only a few visible characters. Below are different ways to achieve this in Oracle.

---

✅ 1️⃣ Mask Middle Digits (Show First & Last 2 Digits)

SELECT REGEXP_REPLACE('9876543210', '(\d{2})\d{6}(\d{2})', '\1******\2') AS masked_mobile FROM DUAL;

🟢 Output

98******10

🔹 This keeps the first two and last two digits visible and replaces the middle six with ******.

---

✅ 2️⃣ Mask All Except Last 4 Digits

SELECT RPAD('*', LENGTH('9876543210') - 4, '*') || SUBSTR('9876543210', -4) AS masked_mobile FROM DUAL;

🟢 Output

******3210

🔹 Uses RPAD('*', ...) to replace all but the last 4 digits.

---

✅ 3️⃣ Mask Using CASE (Dynamic Length Handling)

SELECT 
    CASE 
        WHEN LENGTH('9876543210') = 10 THEN 'XXXXX' || SUBSTR('9876543210', -5)  -- Mask first 5 digits
        ELSE 'Invalid Number'
    END AS masked_mobile
FROM DUAL;

🟢 Output

XXXXX43210

🔹 Useful if the number length is inconsistent.

---

✅ 4️⃣ General Function to Mask Any Mobile Number

If you frequently need to mask numbers, a function can help.

CREATE OR REPLACE FUNCTION mask_mobile(p_mobile VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    RETURN SUBSTR(p_mobile, 1, 2) || '******' || SUBSTR(p_mobile, -2);
END;
/

Usage:

SELECT mask_mobile('9876543210') FROM DUAL;

🟢 Output

98******10

---

🔹 Summary

Method	SQL Code	Output Example
Mask Middle 6 Digits	REGEXP_REPLACE(mobile, '(\d{2})\d{6}(\d{2})', '\1******\2')	98******10
Mask All Except Last 4	`RPAD('', LENGTH(mobile) - 4, '')
Mask First 5 Digits	`CASE WHEN LENGTH(mobile) = 10 THEN 'XXXXX'
Custom Function	CREATE FUNCTION mask_mobile(p_mobile)	98******10

🔹 Masking Aadhaar Number in Oracle

Aadhaar numbers are 12-digit unique identification numbers, and masking them is essential for privacy. Below are different ways to mask an Aadhaar number in Oracle.

---

✅ 1️⃣ Mask Middle Digits (Show First & Last 4 Digits)

SELECT REGEXP_REPLACE('123456789012', '(\d{4})\d{4}(\d{4})', '\1 **** \2') AS masked_aadhaar FROM DUAL;

🟢 Output

1234 **** 9012

🔹 This keeps the first and last 4 digits visible while masking the middle 4 digits with ****.

---

✅ 2️⃣ Mask All Except Last 4 Digits

SELECT RPAD('*', LENGTH('123456789012') - 4, '*') || SUBSTR('123456789012', -4) AS masked_aadhaar FROM DUAL;

🟢 Output

********9012

🔹 This replaces all but the last 4 digits with *.

---

✅ 3️⃣ Mask All Except First & Last 2 Digits

SELECT SUBSTR('123456789012', 1, 2) || '********' || SUBSTR('123456789012', -2) AS masked_aadhaar FROM DUAL;

🟢 Output

12********12

🔹 Useful when only minimal information needs to be shown.

---

✅ 4️⃣ Create a Function for Aadhaar Masking

If you frequently need to mask Aadhaar numbers, a function can help.

CREATE OR REPLACE FUNCTION mask_aadhaar(p_aadhaar VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    RETURN SUBSTR(p_aadhaar, 1, 4) || ' **** ' || SUBSTR(p_aadhaar, -4);
END;
/

Usage:

SELECT mask_aadhaar('123456789012') FROM DUAL;

🟢 Output

1234 **** 9012

---

🔹 Summary

Method	SQL Code	Output Example
Mask Middle 4 Digits	REGEXP_REPLACE(aadhaar, '(\d{4})\d{4}(\d{4})', '\1 **** \2')	1234 **** 9012
Mask All Except Last 4	`RPAD('', LENGTH(aadhaar) - 4, '')
Mask All Except First & Last 2	`SUBSTR(aadhaar, 1, 2)
Custom Function	CREATE FUNCTION mask_aadhaar(p_aadhaar)	1234 **** 9012

Let me know if you need modifications! 🚀