I have a column (varchar2) in one of the tables in Oracle12c, which I need to mask with an update statement. The table has around 1 million records all of which needs to be updated, so I think a direct SQL update would be much better here rather than PL/SQL block.
Masking Conditions:
- Each word in the value should retain its 1st and last characters
- Remaining each character in the word should be replaced by "x"
- Overall length of the value should remain same as original value
Tried with regex_replace but unable to sort out the exact regex which I can use to achieve above.
SELECT REGEXP_REPLACE(column1, '(\w)(\w+)(\w)', '\1' || RPAD('x', LENGTH('\2'), 'x') || '\3') AS transformed_col1
FROM table1;
Tried to get the length of 2nd part, but LENGTH('\2') is always treated as literal values and always results in 2, thus replacing the middle values by 2 x's.
Sample Values:
- This is a sample value
- Another example of string
Expected Output:
- Txxs is a sxxxxe vxxxe
- Axxxxxr exxxxxe of sxxxxg