Oracle Chinese user name Data desensitization length unchanged, rpad function use

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