Oracle clob column rtf2text function;
CREATE OR REPLACE FUNCTION Rtf2Txt
(
pRtf varchar2
)
return nvarchar2 is
vPos1 int;
vPos2 int;
vPos3 int;
vPos4 int;
vTmp int;
vText varchar2(4000);
begin
vText := pRtf;
if vText is null then
return vText;
end if;
vPos1 := instr(vText, '{', +1);
vPos2 := instr(vText, '}', -1);
if vPos1 > 0 and vPos2 > 0 then
vText := substr(vText, vPos1 +1, vPos2 - vPos1 -1);
end if;
while 1 = 1 loop
vPos2 := instr(vText, '}', +1);
vPos1 := instr(vText, '{', (length(vText) - vPos2) *-1 -1);
if vPos1 > 0 and vPos2 > 0 and vPos1 < vPos2 then
vText := substr(vText, 1, vPos1 -1) || substr(vText, vPos2 +1, length(vText) - vPos2);
else
exit;
end if;
end loop;
vText := replace(vText, '\pard', '');
vText := replace(vText, chr(13), '');
vText := replace(vText, chr(10), '');
vText := replace(vText, '\par', chr(13));
while length(vText) > 0 and substr(vText, 1, 1) IN (' ', CHR(13), CHR(10)) loop
vText := substr(vText, 2, length(vText) -1);
end loop;
while length(vText) > 0 and substr(vText, length(vText), 1) IN (' ', CHR(13), CHR(10)) loop
vText := substr(vText, 1, length(vText) -1);
end loop;
vPos2 := 1;
while 1 = 1 loop
vPos1 := instr(vText, '\', vPos2);
if vPos1 = 0 then
exit;
end if;
if substr(vText, vPos1 +1, 1) = '\' then
vPos2 := vPos1 +2;
continue;
end if;
if substr(vText, vPos1 +1, 1) = '''' then
vTmp := to_number(substr(vText, vPos1 +2, 2), 'xx');
vText := substr(vText, 1, vPos1 -1) ||chr(vTmp)|| substr(vText, vPos1 +4, length(vText) - vPos1 -3);
vPos2 := vPos1 +1;
continue;
end if;
vPos2 := instr(vText, '\', vPos1 +1);
vPos3 := instr(vText, ' ', vPos1 +1);
vPos4 := instr(vText, chr(13), vPos1 +1);
if vPos4 > 0 and vPos4 < vPos3 then
vPos3 := vPos4;
end if;
if vPos2 = 0 and vPos3 = 0 then
vPos3 := length(vText);
end if;
if vPos2 > 0 and (vPos2 < vPos3 or vPos3 = 0) then
vText := substr(vText, 1, vPos1 -1) || substr(vText, vPos2, length(vText) - vPos2 +1);
vPos2 := vPos1;
end if;
if vPos3 > 0 and (vPos3 < vPos2 or vPos2 = 0) then
vText := substr(vText, 1, vPos1 -1) || substr(vText, vPos3 +1, length(vText) - vPos3);
vPos2 := vPos1;
end if;
end loop;
return vText;
end;
/