Masking in SQL | PART 1

Masking in SQL

First Approach
Step 1: Create the Table

CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER(10, 2)
);

Step 2: Insert Sample Data

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 45000.00),
(2, 'Bob', 58000.50),
(3, 'Charlie', 75000.75),
(4, 'David', 91000.00);

Step 3: Query to Hide the Last Two Digits of Salary

SELECT 
    name,
    CONCAT(SUBSTR(TO_CHAR(salary), 1, LENGTH(TO_CHAR(salary)) - 2), '**') 
AS masked_salary
FROM 
    employees;

---

Explanation:

1. TO_CHAR(salary): Converts the salary (a NUMBER) into a string so you can manipulate it using string functions.

2. LENGTH(TO_CHAR(salary))- 2: Calculates the length of the salary string minus two characters to exclude the last two digits.

3. SUBSTR(..., 1, ...): Extracts the string starting from the first character up to the length calculated, effectively removing the last two digits.

4. CONCAT(..., ''):** Concatenates the modified salary string with the ** to mask the last two digits.

---

Second approch

You can achieve the same result in Oracle SQL using both the || (pipe) symbols and the CONCAT function. Below are examples of both approaches for masking the last two digits of the salary.

Sample Table

Let's use the same employees table with the salary column:

CREATE TABLE employees (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    salary NUMBER
);


INSERT INTO employees (employee_id, employee_name, salary) 
VALUES (1, 'Alice', 45000);
INSERT INTO employees (employee_id, employee_name, salary) 
VALUES (2, 'Bob', 55000);
INSERT INTO employees (employee_id, employee_name, salary) 
VALUES (3, 'Charlie', 65000);

---

1. Using || (Pipe Symbols) for Concatenation

The || operator is used to concatenate strings in Oracle SQL.

SELECT employee_id,
       employee_name,
       SUBSTR(salary, 1, LENGTH(salary) - 2) || 'XX' AS masked_salary
FROM employees;

2. Using CONCAT Function for Concatenation

The CONCAT function only concatenates two strings at a time. Therefore, to achieve the same result, you will need to use nested CONCAT calls if more than two strings are involved.

SELECT employee_id,
       employee_name,
       CONCAT(SUBSTR(salary, 1, LENGTH(salary) - 2), 'XX') AS masked_salary
FROM employees;

---

Output:

For both queries (using || or CONCAT), the output will be the same:
Both approaches achieve the same goal of masking the last two digits of the salary in Oracle SQL.