Categories
REGEXP_REPLACE doesn't eliminate all HTML Tags

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
Comments
-
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
1