在 Oracle 中，将字符 B 替换为 V，反之亦然，将字符 Z 替换为 S，反之亦然

Question

我需要替换字符。B 代表 V，反之亦然，Z 代表 S，反之亦然 在甲骨文中

DECLARE
lc_word_so    VARCHAR2 (500);
lc_word_lst   VARCHAR2 (500);
lc_word       VARCHAR2 (500) := 'VBZ';
ln_length     NUMBER         := LENGTH (lc_word);
lc_search     VARCHAR2 (2);
lc_replace    VARCHAR2 (2);
TYPE typ_search IS VARRAY (6) OF VARCHAR2 (1);
arr_search    typ_search := typ_search ('B','V','S','Z');
BEGIN
IF ln_length > 0 THEN
lc_word_so := NULL;
FOR i IN 1 .. arr_search.COUNT LOOP
IF MOD (i, 2) = 0 THEN
lc_search := arr_search (i);
lc_replace := arr_search (i - 1);
ELSE
lc_search := arr_search (i);
lc_replace := arr_search (i + 1);
END IF;
FOR j IN 0 .. ln_length LOOP
lc_word_lst := lc_word_so;
lc_word_so := REGEXP_REPLACE (lc_word, lc_search, lc_replace, 1, j, 'i');
IF lc_word_so = lc_word THEN
EXIT;
ELSE
IF (lc_word_lst IS NULL OR lc_word_lst != lc_word_so) THEN
DBMS_OUTPUT.put_line (lc_word_so);
END IF;
END IF;
END LOOP;
END LOOP;
END IF;
END;

我期望输出： 论坛 烧烤 BVS BVZ VBS 某人？ VVS VVZ

但实际输出是： VVZ 烧烤 VBS

Answer 1

您可以使用内置功能执行此操作。translate()函数将第一种模式('BVSZ'(中的字符替换为第二种模式中偏移量相同的字符 -'VBZS'。未包含在第一个模式中的字符将被忽略：

with cte as (
select 'BNV' as str from dual union all
select 'ZXS' as str from dual union all
select 'BBS' as str from dual
)
select str
,translate(str, 'BVSZ', 'VBZS') as trns
from cte

Answer 2

这是使用translate的解决方案，但它看起来不专业：

SELECT TRANSLATE ('VBZ', 'B', 'V') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'V', 'B') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'S', 'Z') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'Z', 'S') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'BV', 'VB') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'VB', 'BV') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'SZ', 'ZS') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'ZS', 'SZ') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'BS', 'VZ') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'VZ', 'BS') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'SB', 'ZV') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'ZV', 'SB') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'BV', 'VB') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'VBZ', 'BVS') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'VZS', 'BSZ') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'BZS', 'VSZ') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'BVZS', 'VBSZ') FROM DUAL UNION SELECT TRANSLATE ('VBZ', 'VBZS', 'BVSZ') FROM DUAL