Some of the data we collect is entered via a WYSIWYG editor.  Therefore formatting such as bold, italic etc is translated into HTML.

The text could also contain special characters/accented characters, again these are translated into their html equivalents.

In CF Report Builder, I have set the XHTML Text formatting to TRUE.  However the report won't render the text in these boxes.  From what I can see it doesn't like the '&' character (ampersand).

I have read elsewhere that report builder is limited in the characters etc that it can read and that I should translate the values.  I ended up doing this in the database to try and save some time.

CREATE

OR REPLACE FUNCTION translatexhtmltoasci(var_text IN appendix3%TYPE)

RETURN clob

clob;

BEGIN

replace(replace(replace(replace(replace(replace(replace(replace(replac e(replace(replace(replace(replace(

replace(replace(replace(replace(replace(replace(replace(replace(replac e(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(

, chr(38) || 'lt;', chr(60))

, chr(38) || 'gt;', chr(62))

, chr(38) || 'euro;', chr(49792))

, chr(38) || 'iexcl;', chr(49825))

, chr(38) || 'cent;', chr(49826))

, chr(38) || 'pound;', chr(49827))

, chr(38) || 'curren;', chr(49828))

, chr(38) || 'yen;', chr(49829))

, chr(38) || 'brvbar;', chr(49830))

, chr(38) || 'uml;', chr(49832))

, chr(38) || 'copy;', chr(49833))

, chr(38) || 'ordf;', chr(49834))

, chr(38) || 'laquo;', chr(49835))

, chr(38) || 'not;', chr(49836))

, chr(38) || 'shy;', chr(49837))

, chr(38) || 'reg;', chr(49838))

, chr(38) || 'macr;', chr(49839))

, chr(38) || 'deg;', chr(49840))

, chr(38) || 'plusmn;', chr(49841))

, chr(38) || 'sup2;', chr(49842))

, chr(38) || 'sup3;', chr(49843))

, chr(38) || 'acute;', chr(49844))

, chr(38) || 'micro;', chr(49845))

, chr(38) || 'para;', chr(49846))

, chr(38) || 'middot;', chr(49847))

, chr(38) || 'cedil;', chr(49848))

, chr(38) || 'sup1;', chr(49849))

, chr(38) || 'ordm;', chr(49850))

, chr(38) || 'raquo;', chr(49851))

, chr(38) || 'frac14;', chr(49852))

, chr(38) || 'frac12;', chr(49853))

, chr(38) || 'frac34;', chr(49854))

, chr(38) || 'iquest;', chr(49855))

, chr(38) || 'Agrave;', chr(50048))

, chr(38) || 'Aacute;', chr(50049))

, chr(38) || 'Acirc;', chr(50050))

, chr(38) || 'Atilde;', chr(50051))

, chr(38) || 'Auml;', chr(50052))

, chr(38) || 'Aring;', chr(50053))

, chr(38) || 'AElig;', chr(50054))

, chr(38) || 'Ccedil;', chr(50055))

, chr(38) || 'Egrave;', chr(50056))

, chr(38) || 'Eacute;', chr(50057))

, chr(38) || 'Ecirc;', chr(50058))

, chr(38) || 'Euml;', chr(50059))

, chr(38) || 'Igrave;', chr(50060))

, chr(38) || 'Iacute;', chr(50061))

, chr(38) || 'Icirc;', chr(50062))

, chr(38) || 'Iuml;', chr(50063))

, chr(38) || 'ETH;', chr(50064))

, chr(38) || 'Ntilde;', chr(50065))

, chr(38) || 'Ograve;', chr(50066))

, chr(38) || 'Oacute;', chr(50067))

, chr(38) || 'Ocirc;', chr(50068))

, chr(38) || 'Otilde;', chr(50069))

, chr(38) || 'Ouml;', chr(50070))

, chr(38) || 'times;', chr(50071))

, chr(38) || 'Oslash;', chr(50072))

, chr(38) || 'Ugrave;', chr(50073))

, chr(38) || 'Uacute;', chr(50074))

, chr(38) || 'Ucirc;', chr(50075))

, chr(38) || 'Uuml;', chr(50076))

, chr(38) || 'THORN;', chr(50078))

, chr(38) || 'szlig;', chr(50079))

, chr(38) || 'agrave;', chr(50080))

, chr(38) || 'aacute;', chr(50081))

, chr(38) || 'acirc;', chr(50082))

, chr(38) || 'atilde;', chr(50083))

, chr(38) || 'auml;', chr(50084))

, chr(38) || 'aring;', chr(50085))

, chr(38) || 'aelig;', chr(50086))

, chr(38) || 'ccedil;', chr(50087))

, chr(38) || 'egrave;', chr(50088))

, chr(38) || 'eacute;', chr(50089))

, chr(38) || 'ecirc;', chr(50090))

, chr(38) || 'euml;', chr(50091))

, chr(38) || 'igrave;', chr(50092))

, chr(38) || 'iacute;', chr(50093))

, chr(38) || 'icirc;', chr(50094))

, chr(38) || 'iuml;', chr(50095))

, chr(38) || 'eth;', chr(50096))

, chr(38) || 'ntilde;', chr(50097))

, chr(38) || 'ograve;', chr(50098))

, chr(38) || 'oacute;', chr(50099))

, chr(38) || 'ocirc;', chr(50100))

, chr(38) || 'otilde;', chr(50101))

, chr(38) || 'ouml;', chr(50102))

, chr(38) || 'divide;', chr(50103))

, chr(38) || 'oslash;', chr(50104))

, chr(38) || 'ugrave;', chr(50105))

, chr(38) || 'uacute;', chr(50106))

, chr(38) || 'ucirc;', chr(50107))

, chr(38) || 'uuml;', chr(50108))

, chr(38) || 'yacute;', chr(50109))

, chr(38) || 'thorn;', chr(50110))

, chr(38) || 'yuml;', chr(50111))

, chr(38) || 'quot;', chr(34))

, chr(38) || 'amp;', chr(38))

, chr(38) || 'apos;', chr(39))

, chr(38) || 'nbsp;', chr(32))

, chr(38) || 'sect;', chr(49831))

, chr(38) || 'Yacute;', chr(50077))

, chr(38) || 'sbquo;', chr(49794))

, chr(38) || 'fnof;', chr(49795))

, chr(38) || 'bdquo;', chr(49796))

, chr(38) || 'hellip;', chr(49797))

, chr(38) || 'dagger;', chr(49798))

, chr(38) || 'Dagger;', chr(49799))

, chr(38) || 'circ;', chr(49800))

, chr(38) || 'permil;', chr(49801))

, chr(38) || 'Scaron;', chr(49802))

, chr(38) || 'lsaquo;', chr(49803))

, chr(38) || 'OElig;', chr(49804))

, chr(38) || 'lsquo;', chr(49809))

, chr(38) || 'rsquo;', chr(49810))

, chr(38) || 'ldquo;', chr(49811))

, chr(38) || 'rdquo;', chr(49812))

, chr(38) || 'bull;', chr(49813))

, chr(38) || 'ndash;', chr(49814))

, chr(38) || 'mdash;', chr(49815))

, chr(38) || 'tilde;', chr(49816))

, chr(38) || 'trade;', chr(49817))

, chr(38) || 'scaron;', chr(49818))

, chr(38) || 'rsaquo;', chr(49819))

, chr(38) || 'oelig;', chr(49820))

, chr(38) || 'Yuml;', chr(49823))

var_translated_text

dual;

RETURN var_translated_text;

translatexhtmltoasci;

/

However my question is, how can I output the ampersand character? I've tried replacing it for chr(38) but it just outputs the & and leaves the rest of the text unrendered.

eg)  The output in report builder becomes something like this:

...

E 300 L-Ascorbic acid in O.J. L 254 of 01/01/2009, p.68<p> </p><p> </p></li></p><BR/><b>Removal</b><br><p>The entries struck through in edition 98 are now

removed.</p><BR/><b>Note</b><br><p>tests ₂m^2.</p><p>1233456 <strong>bold </strong><u>underline </u><em>italic</em></p><ul><li>bullet

1</li><li>bullet 2</li><li>bullet 3</li></ul><p>aeiou mù^$*)àç_è-(é</p><p>2 ù</p><p>Michael & sons.</p><BR/>