Reputation: 764
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?
Upvotes: 0
Views: 3340
Reputation: 764
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'
);
Upvotes: 1
Reputation: 60312
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;
Upvotes: 1
Reputation: 714
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;
Upvotes: 1