Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Masking data with '*'

BeefStuMay 24 2023

have the following strings, which vary in length(see below). How can I mask the data with '*' with the exception of the first and last characters.

Can this be done with regexp_replace? Thanks to all who answer.

CREATE TABLE CARDS_TBL (
  CARD_ID NUMBER
    GENERATED BY DEFAULT AS IDENTITY,
  CARD_STR VARCHAR2(16) NOT NULL,
  PRIMARY KEY (CARD_ID)
);


INSERT INTO CARDS_TBL(CARD_STR) VALUES('4024007187788590');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5432223398564536');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('5430434');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('406755227854');
INSERT INTO CARDS_TBL(CARD_STR) VALUES('54312318');


Desired output:

CARDS_STR MASKED_STR
4024007187788590 4**************0
5432223398564536 5**************6
5430434                   5*****4
406755227854        4**********4
54312318                5******8


This post has been answered by L. Fernigrini on May 24 2023
Jump to Answer
Comments
Post Details
Added on May 24 2023
12 comments
829 views