SQL & PL/SQL

hi Friends,

I want sensitive data to be masked with ****. I am developing code to handle a business requirement on similar lines but no luck .

I tried regexp_replace but just dont know how to handle all different cases of password. (

For e.g.consider this DDL

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

create table TMP_CLOBDATA

(

ID number,

STR clob

);

insert into TMP_CLOBDATA values (1,'this is my password 4545');

insert into TMP_CLOBDATA values (1,'this is my pass 345345453');

insert into TMP_CLOBDATA values (2,'this is my pwd 3234234234 pass34344');

insert into TMP_CLOBDATA values (3,'I am dude ');

insert into TMP_CLOBDATA values (3,'I am Carmella carmella CARMELLA ');

commit;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

So currently the output looks like :

SQL> select *from TMP_CLOBDATA;

    ID|STR

----------|--------------------------------------------------------------------------------

     1|this is my password 4545

     1|this is my pass 345345453

     2|this is my pwd 3234234234 pass34344

     3|I am  dude

     3|I am  Carmella carmella CARMELLA

I want the data to be of below format

Coming to the topic here is the requirement:

1. I want both password word and the the value to be masked,

2. there can be space or no space between the password & the value ( like in id=2). Same for dude & Carmella keyword

3. there are can be multiple occurrences of such a pattern in the clob . There is no fixed number to that. So i can have Carmella or password 10-20 times also. So i want to develop solution which doesnt put the limit on number of occurances.

4. The password word can be pwd,pass,password . Actually password by anything followed by it untill the end of that word.

5. DB version:11.2.0.4.0

Please suggest how i should proceed with it. Eventually i want to update all this data with *****

Regds.

perfdba