如何将代码包含在oracle中的REPLACE函数中

Question

User@psaraj12在这里用以下代码帮助我在数据库中的字符串中找到ascii字符：

with test (col) as (
select 
'L.A.D' 
from 
dual 
union all 
select 
'L.  .D.' 
from 
dual
) 
select 
col, 
case when max(ascii_of_one_character) >= 65535 then 'NOT OK' else 'OK' end result 
from 
(
select 
col, 
substr(col, column_value, 1) one_character, 
ascii(
substr(col, column_value, 1)
) ascii_of_one_character 
from 
test cross 
join table(
cast(
multiset(
select 
level 
from 
dual connect by level <= length(col)
) as sys.odcinumberlist
)
)
) 
group by 
col
having max(ascii_of_one_character) >= 4000000000;

脚本查找某个范围的字符GROUP，并标记显示它们。

是否可以将其包含在类似类型的REPLACE语句中：

REPLACE(table.column, max(ascii_of_one_character) >= 4000000000, '')

编辑：根据@flyaround的回答，这是我使用的代码更改了一点：

with test (col) as (
select skunden.name1
from skunden
) 
select col
, REGEXP_REPLACE(col, 'max(ascii_of_one_character)>=4000000000', '') as cleaned
, CASE WHEN REGEXP_COUNT(col, 'max(ascii_of_one_character)>=4000000000') > 0 THEN 0 ELSE 1 END as isOk 
from test;

Answer 1

回到您的原始代码，因为我建议的REGEX_RELACE在高代理中不够用。你的方法已经很有效了，所以我跳进去想在这里找到一个解决方案。

MERGE
INTO  skunden   
USING (
select 
id as innerId, 
name as innerName, 
case when max(ascii_of_one_character) >= 65535 then 0 else 1 end isOk, 
listagg(case when ascii_of_one_character <65535 then one_character end , '') within group (order by rn) as cleaned  
from 
(
select
id,
name, 
substr(name, column_value, 1) one_character, 
ascii(
substr(name, column_value, 1)
) ascii_of_one_character 
, rownum as rn
from 
skunden cross 
join table(
cast(
multiset(
select 
level 
from 
dual connect by level <= length(name)
) as sys.odcinumberlist
)
)
) 
group by 
id, name 
having max(ascii_of_one_character) >= 4000000000
)
ON (skunden.id = innerId)
WHEN MATCHED THEN
UPDATE
SET name = cleaned
;

在MERGE中，不能使用引用列进行更新。因此，您应该使用表的唯一键(我在示例中使用了"id")。

结果值为'L。。D代表你的L的范例值。D.

Answer 2

如果我正确回答了您的问题，您希望删除小数位数高于指定值的字符。您可以检查使用REGEXP_REPLACE，例如：

with test (col) as (
select 
'L.A.D' 
from 
dual 
union all 
select 
'L.  .D.' 
from 
dual
) 
select col
, REGEXP_REPLACE(col, '[^u00010000-u0010FFFF]+$', '') as cleaned 
, CASE WHEN REGEXP_COUNT(col, '[^u00010000-u0010FFFF]+$') > 0 THEN 0 ELSE 1 END as isOk 
from test;