Replace (translate) one char to many

Question

I have a string. In this string i need replace all special characters (0-31 codes) with chosen representation. Representations may be of different formats. May be \x??, or \0???, or 10,13 -> \n, 9 -> \t and all others characters are converting to null. Summary - i need find all symbols with 0-31 codes and replace all of them for appropriate representation, which can be zero or more symbols.

Solution should work in 9.2 Oracle (thats mean no regexp) and shuld be very fast.

I know that TRANSLATE function is really fast. Buth there i can`t replace one symbol for many. I can replace only one by one.

My barbarian (and easy) solution is to create lists with 32 elements for each representation. Than for chosen representation make a loop over the list. Inside the loop call REPLACE function. In this case i would always call replace 32-times. I think that is expencive.

Are you have any idea?

Answer 1

That's my "barbarian", but effective solution. it's main part:

res :=
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(
                                                                                            REPLACE(
                                                                                                REPLACE(
                                                                                                    REPLACE(
                                                                                                        REPLACE(
                                                                                                            REPLACE(
                                                                                                                REPLACE(
                                                                                                                    REPLACE(
                                                                                                                        REPLACE(
                                                                                                                            REPLACE(
                                                                                                                                REPLACE(
                                                                                                                                    p_txt,
                                                                                                                                    CHR(0),
                                                                                                                                    '\0x00'
                                                                                                                                ),
                                                                                                                                CHR(1),
                                                                                                                                '\0x01'
                                                                                                                            ),
                                                                                                                            CHR(2),
                                                                                                                            '\0x02'
                                                                                                                        ),
                                                                                                                        CHR(3),
                                                                                                                        '\0x03'
                                                                                                                    ),
                                                                                                                    CHR(4),
                                                                                                                    '\0x04'
                                                                                                                ),
                                                                                                                CHR(5),
                                                                                                                '\0x05'
                                                                                                            ),
                                                                                                            CHR(6),
                                                                                                            '\0x06'
                                                                                                        ),
                                                                                                        CHR(7),
                                                                                                        '\0x07'
                                                                                                    ),
                                                                                                    CHR(8),
                                                                                                    '\0x08'
                                                                                                ),
                                                                                                CHR(9),
                                                                                                '\0x09'
                                                                                            ),
                                                                                            CHR(10),
                                                                                            '\0x0A'
                                                                                        ),
                                                                                        CHR(11),
                                                                                        '\0x0B'
                                                                                    ),
                                                                                    CHR(12),
                                                                                    '\0x0C'
                                                                                ),
                                                                                CHR(13),
                                                                                '\0x0D'
                                                                            ),
                                                                            CHR(14),
                                                                            '\0x0E'
                                                                        ),
                                                                        CHR(15),
                                                                        '\0x0F'
                                                                    ),
                                                                    CHR(16),
                                                                    '\0x10'
                                                                ),
                                                                CHR(17),
                                                                '\0x11'
                                                            ),
                                                            CHR(18),
                                                            '\0x12'
                                                        ),
                                                        CHR(19),
                                                        '\0x13'
                                                    ),
                                                    CHR(20),
                                                    '\0x14'
                                                ),
                                                CHR(21),
                                                '\0x15'
                                            ),
                                            CHR(22),
                                            '\0x16'
                                        ),
                                        CHR(23),
                                        '\0x17'
                                    ),
                                    CHR(24),
                                    '\0x18'
                                ),
                                CHR(25),
                                '\0x19'
                            ),
                            CHR(26),
                            '\0x1A'
                        ),
                        CHR(27),
                        '\0x1B'
                    ),
                    CHR(28),
                    '\0x1C'
                ),
                CHR(29),
                '\0x1D'
            ),
            CHR(30),
            '\0x1E'
        ),
        CHR(31),
        '\0x1F'
    );

Answer 2

This would probably perform just as well, and be easier to read and maintain:

function trans (p_in in varchar2) return varchar2 is
   l_out varchar2(32767) := p_in;
begin
   if length(l_out) > 0 then
      for i in 0..31 loop
         l_out := REPLACE(l_out, CHR(i), '\0x' || to_char(i,'FM0X'));
      end loop;
   end if;
   return l_out;
end trans;

Answer 3

You could use decode. Documentation can be found here:

http://www.techonthenet.com/oracle/functions/decode.php

If you're using it in a pl/sql procedure or function you could use it like this:

SELECT decode(your_string, 'var1', 'repl1', 'var2', 'repl2', 'varN', 'replN')
INTO l_decoded_string
FROM dual;