Oracle SQL select 语句中的数据屏蔽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18295085/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data masking in Oracle SQL select statement
提问by Ted
Without using PL/SQL, is it possible to do data masking in SELECT statement?
在不使用PL/SQL的情况下，是否可以在SELECT语句中进行数据屏蔽？
For example:
例如：
(AS-IS)SELECT 'this is a string' from DUAL;
（按原样）SELECT 'this is a string' from DUAL;
this is a string
这是一个字符串
(TO-BE) SELECT 'this is a string' from DUAL;
(未来)SELECT 'this is a string' from DUAL;
xxxx xx x xxxxxx
xxx xxx xxx xxx
回答by Ed Gibbs
REGEXP_REPLACEcan do this:
REGEXP_REPLACE可以这样做：
SELECT REGEXP_REPLACE('this is a string', '\w', 'x') FROM DUAL;
This replaces all non-whitespace characters with an x. To replace letters only, try this:
这会将所有非空白字符替换为x. 要仅替换字母，请尝试以下操作：
SELECT REGEXP_REPLACE('this is a string', '[A-Za-z]', 'x') FROM DUAL;
回答by Vinoth Durairaj
You can create user defined function as below and call that function in your query to mask the data.
您可以创建如下用户定义的函数并在查询中调用该函数来屏蔽数据。
create or replace function scrubbing(word in varchar2)
return varchar2
as
each_var char(2);
final_val varchar2(100);
complete_data varchar2(4000);
each_word varchar2(1000);
cursor val is select substr(replace(word,' ','#'),-level,1) from dual connect by level<=length(word);
begin
open val;
--final_val:= '';
loop
fetch val into each_var;
exit when val%NOTFOUND;
--dbms_output.put_line(each_var);
final_val := trim(final_val)||trim(each_var);
--dbms_output.put_line(final_val);
select regexp_substr(final_val,'[A-Za-z]+') into each_word from dual;
select replace(translate(final_val,each_word,dbms_random.string('L',length(word))),'#',' ') into complete_data from dual;
end loop;
return complete_data;
end;
