如何将ASCII字符检测代码整合到Oracle的REPLACE函数中？

问题背景 #

之前通过代码检测字符串中符合特定码点范围的字符，现在需要将检测逻辑整合到替换函数中，直接移除不符合要求的字符。之前尝试的REGEXP_REPLACE写法错误，无法实现预期效果。

原检测代码回顾 #

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;

注：原代码中ASCII()函数仅能返回单字节字符的ASCII值，对于多字节/高码点字符，应使用UNICODE()函数获取正确的Unicode码点。

正确实现方式 #

方式一：拆分字符过滤后拼接（精准适配任意码点范围）

该方法通过拆分字符串为单个字符，过滤掉不符合要求的字符后重新拼接，逻辑清晰且支持任意码点范围：

WITH test (col) AS (
    SELECT skunden.name1 FROM skunden
),
split_chars AS (
    SELECT
        col,
        SUBSTR(col, column_value, 1) AS single_char,
        UNICODE(SUBSTR(col, column_value, 1)) AS char_code
    FROM test
    CROSS JOIN TABLE(
        CAST(MULTISET(
            SELECT LEVEL FROM dual CONNECT BY LEVEL <= LENGTH(col)
        ) AS sys.odcinumberlist)
    )
)
SELECT
    col,
    -- 只保留码点小于4000000000的字符，拼接成清理后的字符串
    LISTAGG(CASE WHEN char_code < 4000000000 THEN single_char END, '') 
        WITHIN GROUP (ORDER BY column_value) AS cleaned_col,
    -- 标记是否存在不符合要求的字符
    CASE WHEN MAX(char_code) >= 4000000000 THEN 0 ELSE 1 END AS isOk
FROM split_chars
GROUP BY col;

方式二：使用REGEXP_REPLACE（适合Unicode标准范围场景）

若目标码点属于Unicode标准范围（如≥65535的补充平面字符），可利用正则表达式的代理对匹配实现快速替换：

WITH test (col) AS (
    SELECT skunden.name1 FROM skunden
)
SELECT
    col,
    -- 替换所有Unicode补充平面字符（码点≥65535）
    REGEXP_REPLACE(col, '[\uD800-\uDBFF][\uDC00-\uDFFF]', '') AS cleaned_col,
    -- 标记是否存在补充平面字符
    CASE WHEN REGEXP_COUNT(col, '[\uD800-\uDBFF][\uDC00-\uDFFF]') > 0 THEN 0 ELSE 1 END AS isOk
FROM test;

说明：Unicode补充平面字符在UTF-16中以代理对形式存在，高代理范围为\uD800-\uDBFF，低代理范围为\uDC00-\uDFFF，正则匹配该范围即可精准定位这类字符。

常见错误说明 #

直接在REGEXP_REPLACE的匹配模式中写入max(ascii_of_one_character)>=4000000000是无效的，因为正则模式仅匹配字符串的字面内容，无法执行SQL表达式逻辑，这类写法只会替换掉字符串中恰好等于该表达式文本的内容，而非检测字符的码点。

内容的提问来源于stack exchange，提问作者DarkBlade