CODEWITHSUNDEEP
htmlsqloracle-database
user2552670
user2552670

Reputation: 41

HTML Entity decoding to Special characters

I want to display special symbols in my output. For eg: My text may contain entity codes like &lt;, &gt; etc. I want to display this as <, > in my output. I need to do this in SQL. I googled about this and got a function,

select dbms_xmlgen.convert('ABC <; ',0) from dual

This does the reverse process, it generates the output as 'ABC <'

I tried with decoding but it does not work. I even changed the sql command as, select dbms_xmlgen.convert('ABC <; ',1) from dual, where 1 is for entity_decode, but I don't get the desired output.

Upvotes: 4

Views: 26693

Answers (2)

Jako
Jako

Reputation: 982

Instead of using DBMS_XMLGEN.convert, I used the function UTL_I18N.UNESCAPE_REFERENCE:

SELECT UTL_I18N.UNESCAPE_REFERENCE('ABC &lt; ') FROM DUAL;

result: 

ABC <

More information on the Oracle doc: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_i18n.htm#i998992

Upvotes: 13

Aioros
Aioros

Reputation: 4383

Try something like:

SELECT DBMS_XMLGEN.CONVERT('ABC &lt; ', DBMS_XMLGEN.ENTITY_DECODE) FROM DUAL

Also, see the Oracle docs for that.

EDIT:

Ok, so apparently this is a bug in some Oracle versions (9.2.0.1 and 10.1.0.2, as it seems). Somebody solved it by wrapping the function. I don't know how that's supposed to solve it, but it my be worth trying. Create a function like this:

CREATE OR REPLACE FUNCTION
   xml_decode(
      i_xml_string IN VARCHAR2
   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN
      DBMS_XMLGEN.convert(
         i_xml_string,
         DBMS_XMLGEN.ENTITY_DECODE
      );
END;

And use it instead:

SELECT xml_decode('ABC &lt; ') FROM DUAL;

Let us know if that works.

Upvotes: 2

Related Questions