Masking in SQL | PART 2

#sql #devops #backend #fullstack

Masking in sql

Let's dive deeper into the explanation and include the use of the CONCAT function with the || (pipe) operator in Oracle for concatenation. In Oracle, both CONCAT and || can be used to concatenate strings.

We will use the SUBSTR function to extract parts of the salary and then use either the CONCAT function or the || operator to append the masked characters (**). Below is the detailed explanation with the pipe operator and an alternative with CONCAT.

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

Here, we'll use both the pipe operator || for concatenation and the SUBSTR function to manipulate the salary string.

Using || (Pipe Operator):

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

Using CONCAT:

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

Detailed Explanation:

1. TO_CHAR(salary):

The salary is stored as a NUMBER data type in Oracle. To manipulate the salary as a string, we first convert it to a string using the TO_CHAR function.
Example: If the salary is 45000.00, TO_CHAR(salary) will return '45000.00'.

2. LENGTH(TO_CHAR(salary)) - 2:

We calculate the length of the salary string and subtract 2 to remove the last two characters.
Example: For '45000.00', the length is 8. Subtracting 2 gives 6.

3. SUBSTR(TO_CHAR(salary), 1, LENGTH(TO_CHAR(salary)) - 2):

The SUBSTR function extracts a substring starting from position 1 up to the calculated length (excluding the last two characters).
Example: If the salary is '45000.00', SUBSTR(TO_CHAR(salary), 1, 6) returns '45000'.

4. Concatenation using ||:

The || operator is used in Oracle SQL to concatenate strings.
In this case, we are appending ** to the substring of the salary to mask the last two digits.
Example: '45000' || '' results in '450'.

5. Concatenation using CONCAT: