DEV Community

Pranav Bakare
Pranav Bakare

Posted on

1

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)
);

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay