Replace (translate) one char with many
I have a string. In this line, I need to replace all special characters (0-31 codes) with the selected representation. The views can be of different formats. Maybe \ x ??, or \ 0 ???, or 10.13 → \ n, 9 → \ t, and all other characters are converted to zero. To recap - I need to find all characters with 0-31 codes and replace them all for the appropriate representation, which can be zero or more characters.
The solution should work in Oracle 9.2 (that doesn't mean regexp) and shuld will be very fast.
I know the TRANSLATE function is very fast. Buth there i can't replace one character for many. I can only replace one for one.
My barbaric (and simple) solution is to create lists with 32 items for each view. Than the selected view does the loop over the list. Call the REPLACE function inside the loop. In this case, I would always call replace 32 times. I think it is expensive.
Do you have any ideas?
a source to share
This is my "barbaric" but effective solution. its 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'
);
a source to share
You can use decoding. The documentation can be found here:
http://www.techonthenet.com/oracle/functions/decode.php
If you use it in a pl / sql procedure or function, you can use it like this:
SELECT decode(your_string, 'var1', 'repl1', 'var2', 'repl2', 'varN', 'replN')
INTO l_decoded_string
FROM dual;
a source to share
This will probably work 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;
a source to share