Partial Masking in SQL - Oracle

Question

Given table has debit card numbers. I want to write a code to mask the debit card numbers in which the middle 4 numbers are replaced with X. The table is as follows:

I want the following output:

I have written the following code but it is giving errors:

UPDATE
Debit_card_master
SET
Debit_card = LEFT(Debit_card, 4) + REPLICATE(‘x’, 4) + RIGHT(Debit_card, 4) ;

Also, can anyone help me with the correct MySQL code for the same?

Oracle uses || for string concatenation. Voting to close as a typo. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 1, 2021 at 13:02

Gordon Linoff · Accepted Answer · 2021-02-01 13:04:11Z

0

Your code looks kind of appropriate for MySQL (although not quite). In Oracle, you can use regexp_replace():

regexp_replace(debit_card, '^(....)(....)', '\1xxxx')

You can test this first. In the update:

UPDATE Debit_card_master
    SET debit_card = regexp_replace(debit_card, '^(....)(....)', '\1xxxx');

Note: This assumes that the number is always 12 characters. Or, more specifically, it replaces the characters in position 5-8.

answered Feb 1, 2021 at 13:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Khushi Thakkar Over a year ago

PDATE Debit_card_master SET debit_card = regexp_replace(debit_card, '^(....)(....)', '\1xxxx'); In the above command, what does this mean? '^(....)(....)' - Do I have to replace it with something?

Gordon Linoff Over a year ago

That is the regular expression pattern. It divides the first eight characters into two groups. Then this is replaced by the first group and four 'x's.

Collectives™ on Stack Overflow

Partial Masking in SQL - Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related