HTML entity decoding with oracle DBMS_XMLGEN.convert

Question

Here, I am trying to convert the value, where I don't understand I am going wrong. Your help is highly appreciated!

Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;

to

Here I am, THis is me there's nowhere else on earth I rather be Here I am,

For this I used the following query which didn't work, Can you please tell me where I am wrong?

select DBMS_XMLGEN.convert(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&1円'), 1 ) as message from dual;

• Raj: WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&1円'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x
• pratikch: @Raj Can you please put this in answer that works! Thank you very much.

Answer 1

Here it is

WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&1円'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x;