Replace (translate) one char to many
640565May 14 2009 — edited May 15 2009I 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?