我在屏蔽以下輸入的記錄時遇到問題：
輸入：
The Domain Value is 32456 is getting from Spain to Madrid for String value differently . and this is the data for all kind of variable.
輸出：
The ************************************************************ring value differently . and this is the data for all kind of variable.
基本上在上面的輸入“域值”是固定字，所以我們需要屏蔽從域值開始到下一個 60 長度（固定）的資料。
我正在嘗試下面的查詢，但它沒有屏蔽下一個 60 長度
SELECT
CASE
WHEN start_pos1 > 0 THEN SUBSTR( col, 1, start_pos1 - 1)
|| RPAD('*', end_pos1 - start_pos1, '*')
|| SUBSTR(col, end_pos1)
ELSE col
END AS col_new
FROM (
SELECT
col,
REGEXP_INSTR( col, 'Domain Value([. # -]*\s{60} ) ', 1, 1, 0, NULL) AS start_pos1,
REGEXP_INSTR( col, 'Domain Value([. # -]*\s{60} ) ', 1, 1, 1, NULL) AS end_pos1,
FROM Table
)
uj5u.com熱心網友回復：
看起來像一些子字串的問題：
SQL> with test (col) as
2 (select 'The Domain Value is 32456 is getting from Spain to Madrid for String varies differently . and this is the data for all kind of variable.' from dual)
3 select substr(col, 1, instr(col, 'Domain Value') - 1) ||
4 lpad('*', 60, '*') ||
5 substr(col, instr(col, 'Domain Value') 60) result
6 from test ;
RESULT
--------------------------------------------------------------------------------
The ************************************************************ring varies diff
erently . and this is the data for all kind of variable.
SQL>
uj5u.com熱心網友回復：
如果文本 'Domain Value' 可能在文本中出現多次，一個簡單的 regexp_replace 就可以做到。
with demo (col) as
( select 'The Domain Value is 32456 is getting from Spain to Madrid for String varies differently . and this is the data for all kind of variable.' from dual
union all
select 'The Domain Value is a secret.' from dual
)
select regexp_replace(col, 'Domain Value.{1,48}', rpad('*',60,'*')) as redacted
from demo;
DB小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387379.html