Information security considerations sometimes require data masking for user names.
Fetching Data desensitization for Oracle database
Desensitization rule:
The length is less than 9 characters, only the first 3 characters and the last 3 Chinese characters, the middle of all filled by *.
Length 9 words and above and odd, hidden to the middle 3 words, length 10 words and above and odd, hidden to the middle 4 words.
For example:
Company Name: The world's invincible Super big company's Hangzhou branch
Field Length: 18
After Desensitization: The universe of the Invincible division of the company's Hangzhou branch
The correct answer to be achieved:
Selectt.no,---Company Number Case whenLength (T.name)<=8 ThenSUBSTR (T.name,1,3)||Substr'**',1, Length (T.name)-6)||SUBSTR (T.name,-3,3-------Company name length is definitely greater than 6, generally above 6ElseSUBSTR (T.name,1,round(Length (T.name)/2,0)-2)||Substr'****',1,4-MoD (Length (t.name),2))||SUBSTR (T.name,-(round(Length (T.name)/2,0)-2),round(Length (T.name)/2,0)-2)End)--Company name after---desensitization fromAAA T;
Description
substr (T.name,1,3) The first three words of company name
substr (T.name,-3,3) Three characters after company name
Round (Length (t.name)/2,0)-2 Calculate Company Name * Length of previous part
substr ('* * * *',1,4-mod (Length (t.name),2)) Singular 3 * Single 4 *
Oracle uses the SUBSTR function to recognize Chinese characters, which can be recognized as a Chinese character.
Input:
substr (Hangzhou branch of the Super Big Company of the universe Invincible, 1, 3)
Output:
Universe without
Detour:
Try using the Rpad (or lpad) function in the middle.
It is found that this function has a great problem in the recognition of the length of Chinese text segments.
Rpad (' universe invincible ',ten,'*')
Theoretically, the string length is 10, the "universe invincible" length is 4, the output should be: the Universe invincible ******
In fact, the Oracle reads "Invincible Universe" length of 8, the output is: the Universe invincible * *
Specific information: https://www.cnblogs.com/objectorl/p/rpad-length-issue-in-multibyte-encoding.html
Lpad Function: used in PL/SQL to fill some characters to the left of the source string.
Function parameters:lpad (string1, padded_length, [pad_string])
which
String1: source string
Padded_length: The length of the final returned string, if the length of the final returned string is smaller than the source string, then this function actually truncates the source strings
Pad_string: The character used for padding, can not be filled, default is null character
Oracle Chinese user name Data desensitization length unchanged, rpad function use