Oracle Analytics Cloud and Server Idea Lab

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

REGEXP_REPLACE doesn't eliminate all HTML Tags

Needs Votes
359
Views
1
Comments

Organization Name

UnitedLex Corporation

Description

Hi Team,

I have developed by report based on note "How To Remove All HTML Tags From BIP Report ( Doc ID 2404894.1 )".

Below is the value stored in Data base

Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;

Below SQL Function doesn't remove all HTML tags.

select replace(replace(REGEXP_REPLACE(note.NOTE_TXT, '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ') as note_text
from zmm_notes note,ra_customer_trx_all rcta
WHERE note.source_object_code = 'AR_TRANSACTION'
and rcta.customer_trx_id = note.source_object_uid
and rcta.trx_number = '38723'

I have also tested the same database value using Dual.

select replace(replace(REGEXP_REPLACE('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;', '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ')
from dual;

select 
TRIM(replace(replace(replace(TO_CHAR(DBMS_LOB.SUBSTR('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;'
,3800,1)),CHR(10),' '),CHR(13),' '),'<br/>','')) from dual;

But still, it is not clearing HTML Tags.

Hence, I am approaching you to let me know, if this can be handled as Enhancement.

Thank you.

Regards,
Karthik.

Use Case and Business Need

Hi Team,

I have developed by report based on note "How To Remove All HTML Tags From BIP Report ( Doc ID 2404894.1 )".

Below is the value stored in Data base

Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;

Below SQL Function doesn't remove all HTML tags.

select replace(replace(REGEXP_REPLACE(note.NOTE_TXT, '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ') as note_text
from zmm_notes note,ra_customer_trx_all rcta
WHERE note.source_object_code = 'AR_TRANSACTION'
and rcta.customer_trx_id = note.source_object_uid
and rcta.trx_number = '38723'

I have also tested the same database value using Dual.

select replace(replace(REGEXP_REPLACE('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;', '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ')
from dual;

select 
TRIM(replace(replace(replace(TO_CHAR(DBMS_LOB.SUBSTR('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;'
,3800,1)),CHR(10),' '),CHR(13),' '),'<br/>','')) from dual;

But still, it is not clearing HTML Tags.

Hence, I am approaching you to let me know, if this can be handled as Enhancement.

Thank you.

Regards,
Karthik.

More details

Hi Team,

I have developed by report based on note "How To Remove All HTML Tags From BIP Report ( Doc ID 2404894.1 )".

Below is the value stored in Data base

Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;

Below SQL Function doesn't remove all HTML tags.

select replace(replace(REGEXP_REPLACE(note.NOTE_TXT, '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ') as note_text
from zmm_notes note,ra_customer_trx_all rcta
WHERE note.source_object_code = 'AR_TRANSACTION'
and rcta.customer_trx_id = note.source_object_uid
and rcta.trx_number = '38723'

I have also tested the same database value using Dual.

select replace(replace(REGEXP_REPLACE('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;', '<[^>]+>|\&(nbsp;)|(amp;)', ''),chr(13),''),chr(10),' ')
from dual;

select 
TRIM(replace(replace(replace(TO_CHAR(DBMS_LOB.SUBSTR('Invoice is submitted on 5/6/2019. As per the sales there is request for credit memo regarding the invoice under the project. Under follow up with Project Accountant Joy Sanudo. table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl29 {text-align:center; border:.5pt solid gray;} ;'
,3800,1)),CHR(10),' '),CHR(13),' '),'<br/>','')) from dual;

But still, it is not clearing HTML Tags.

Hence, I am approaching you to let me know, if this can be handled as Enhancement.

Thank you.

Regards,
Karthik.

Original Idea Number: e658561603

4
4 votes

Needs Votes · Last Updated

Comments

  • Rank 1 - Community Starter

    We wish to add the AR_TRANSACTION collection notes to AR Aging reports produced with BiPublisher. Rather than type an entire emailed response, users copy and paste the text from the email into the notes field. This method includes the HTML tags in the note. The HTML tags need to removed for the finished report

Welcome!

It looks like you're new here. Sign in or register to get started.