with T as ( select 'HONGKILDONG' as names from dual ), R as ( --select '*123**6789*' keys from dual select '123**567*90' keys from dual ) select listagg(result_names,'') within group (order by rownum) as result from ( select case when substr(keys,level,1) = '*' then '*' else substr(T.names,level,1) end as result_names from R, T connect by level <= length(names) ) /
마스킹이 * 이고,.. 무식하게 만들면 위처럼 되지 않을까요..
-- 더 좋은 방법이 있을 것 같지만.. 일단 올려봅니다. WITH RULE AS ( SELECT 1 RN, '.(...)..(.{4}).' PAT, '*\1**\2*' REP FROM DUAL UNION ALL -- *123**6789* SELECT 2 RN, '(...)..(...).(..)' PAT, '\1**\2*\3' REP FROM DUAL -- 012**567*90 ), DATA AS ( SELECT 1 DN, 'HONGKILDONG' VAL FROM DUAL UNION ALL SELECT 2 DN, 'ABCDEFGHIJK' VAL FROM DUAL UNION ALL SELECT 3 DN, '12345678901' VAL FROM DUAL ) SELECT RN, DN, REGEXP_REPLACE(VAL, PAT, REP) FROM RULE, DATA
-- 재미삼아 reculsive with t# as ( select 'HONGKILDONG' i_str, '*123**6789*' fmt FROM dual ), t(no, o_str, i_str, fmt) AS ( select 1 no, cast (case when substr(fmt,1,1) = '*' then substr(fmt,1,1) else substr(i_str,1,1) end as varchar2(4000))o_str, i_str, fmt from t# union all select no + 1 as no, o_str||case when substr(fmt,no+1,1) = '*' then substr(fmt,no+1,1) else substr(i_str,no+1,1) end o_str, i_str, fmt from t where no < length(i_str) ) select i_str,fmt,o_str from t where no = length(i_str); -- 시간남아 model select i_str,fmt,o_str From dual model dimension by (1 X) measures (0 N,'HONGKILDONG' i_str, '*123**6789*' fmt, cast('' as varchar2(4000)) o_str ) rules iterate (100) until (N[1] = length(i_str[1])) ( o_str[1] = case when ITERATION_NUMBER > 1 then o_str[1] end ||case when substr(fmt[1],ITERATION_NUMBER,1) = '*' then substr(fmt[1],ITERATION_NUMBER,1) else substr(i_str[1],ITERATION_NUMBER,1) end )