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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.