oracle小笔记

 select CASE
                       WHEN ASCIISTR(name) LIKE '%\%'
                            AND LENGTH(name) = 2 THEN
                        '*' || SUBSTR(name, -1) --脱敏姓名中文2个汉字
                       WHEN ASCIISTR(name) LIKE '%\%'
                            AND LENGTH(name) > 2 THEN
                        SUBSTR(name, 1, 1) ||
                        SUBSTR('***************************',
                               1,
                               LENGTH(SUBSTR(name, 2, LENGTH(name) - 2))) ||
                        SUBSTR(name, -1) --脱敏姓名中文大于2个汉字
                       WHEN ASCIISTR(name) NOT LIKE '%\%'
                            AND name NOT LIKE '% %' THEN
                        SUBSTR(name, 1, 1) ||
                        SUBSTR('***************************',
                               1,
                               LENGTH(SUBSTR(name, 2, LENGTH(name) - 2))) --脱敏姓名英文(J*********(JohnWilson))
                       WHEN ASCIISTR(name) NOT LIKE '%\%'
                            AND REGEXP_COUNT(name, ' ') = 1 THEN
                        SUBSTR(name, 1, 1) ||
                        SUBSTR('***************************',
                               1,
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ') - 1))) ||
                        SUBSTR(name,
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ')))) --脱敏姓名英文(M****** Jackson(Micheal Jackson))
                       WHEN ASCIISTR(name) NOT LIKE '%\%'
                            AND REGEXP_COUNT(name, ' ') = 2 THEN
                        SUBSTR(name, 1, 1) ||
                        SUBSTR('***************************',
                               1,
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' '))) - 1) ||
                        SUBSTR(name,
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ') - 1)) + 1,
                               2) ||
                        SUBSTR('***************************',
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ') + 2)),
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ', 1, 2))) -
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ') + 2))) ||
                        SUBSTR(name,
                               LENGTH(SUBSTR(name, 2, INSTR(name, ' ', 1, 2)))) --脱敏姓名英文 G***** W***** Bush(George Walker Bush)
                       ELSE
                        SUBSTR(name, 1, 1) || '***********'
                   END AS CUST_NAME
                   from dual;