Replace characters B for V and vice versa, Z for S and vice versa in Oracle

Question

I need to replace characters. B for V and vice versa, Z for S and vice versa in Oracle

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;

I expect the output: BBS BBZ BVS BVZ VBS VBZ VVS VVZ

But the actual output is: VVZ BBZ VBS

Answer 1

This is the solution using translate, but it looks unprofessional:

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