Skip to content
  • Register

    • Oracle Analytics Cloud and Server Idea Lab

    Home› Oracle Analytics› Oracle Analytics Idea Labs› Oracle Analytics Cloud and Server Idea Lab

    Welcome!

    Sign in to share your experience and connect with the community.
    Log In
    Register

    Quick Links

    Categories

    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
    353
    Views
    1
    Comments
    Rank 1 - Community Starter
    in Oracle Analytics Cloud and Server Idea Lab

    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
    Up
    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.
    Log In
    Register