Home » SQL & PL/SQL » SQL & PL/SQL » how to remove html tags from the text
how to remove html tags from the text [message #684127] Thu, 08 April 2021 07:39 Go to next message
Sekhar6617
Messages: 10
Registered: March 2021
Junior Member
Hi Team,

whenever am selecting the fields from the table using sql, I was getting the HTML tags for one of the field.
Ideally it should be not shown in sql output.
Could you please help me that how to remove HTML tags from the text message.

Thank you.

Regards
Sekhar
Re: how to remove html tags from the text [message #684128 is a reply to message #684127] Thu, 08 April 2021 08:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3054
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just use REGEXP_REPLACE.

SY.
Re: how to remove html tags from the text [message #684129 is a reply to message #684127] Thu, 08 April 2021 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And think about giving feedback and thank people in your topics.

Re: how to remove html tags from the text [message #684132 is a reply to message #684128] Thu, 08 April 2021 10:47 Go to previous messageGo to next message
Sekhar6617
Messages: 10
Registered: March 2021
Junior Member
Hi Solomon,

Thank you for your response.

I have tried with REGEXP_REPLACE function but couldn't see the proper results.
select REGEXP_REPLACE('<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",scan-serif">Unified Support provides.</span></p>', '^(\S*|\W*|\d*)','') from notes_tbl;
Here I am explaining you with example. Assume NOTE_TEXT field has the content like this:
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",scan-serif">Unified Support provides.</span></p>

Ideally this happens when the user saves the entire content of email into this field. In the online pages, it is showing as proper message but when i query back-end data it is showing with HTML commands.

CREATE TABLE NOTES_TBL (
    NOTE_ID int,
    NOTE_NAME varchar(255),
    NOTE_TEXT CLOB
    );
	
INSERT INTO NOTES_TBL VALUES (1458, 'Warning Message', 
'<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",scan-serif">Unified Support provides.</span></p>')	
I should get the output as below without displaying the HTML related commands.


select * from NOTES_TBL


Result:
NOTE_ID	  NOTE_NAME	             NOTE_TEXT
1458	  Warning Message	     Unified Support provides.
Thank you.

Regards
Sekhar
Re: how to remove html tags from the text [message #684135 is a reply to message #684132] Thu, 08 April 2021 12:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3054
Registered: January 2010
Location: Connecticut, USA
Senior Member
SELECT  NOTE_ID,
        NOTE_NAME,
        REGEXP_REPLACE(NOTE_TEXT,'<[^>]+>') NOTE_TEXT
  FROM  NOTES_TBL
/

   NOTE_ID NOTE_NAME       NOTE_TEXT
---------- --------------- ----------------------------------------
      1458 Warning Message Unified Support provides.

SQL>
SY.
Re: how to remove html tags from the text [message #684152 is a reply to message #684135] Fri, 09 April 2021 09:09 Go to previous messageGo to next message
Sekhar6617
Messages: 10
Registered: March 2021
Junior Member
Hi Solomon,

Thanks for your suggestion on the issue. This works perfectly.

Regards
Sekhar
Re: how to remove html tags from the text [message #684170 is a reply to message #684152] Wed, 14 April 2021 07:41 Go to previous messageGo to next message
Sekhar6617
Messages: 10
Registered: March 2021
Junior Member
Hi SOlomon,

Could you please help me to solve below issue.
I am getting line break and blank lines in column data though I used regexp_replace function as given below.

REGEXP_REPLACE(NOTE_TEXT, '<[^>]+>|\&nbsp;|\n|\A','')
But when I uploading the date into excel file note_text breaks out into separate rows.

Ideally, it should be in one cell. Shown in attached issue.png for your reference.

Thank you.

Regards
Sekhar
  • Attachment: issue.png
    (Size: 72.02KB, Downloaded 65 times)
Re: how to remove html tags from the text [message #684171 is a reply to message #684170] Wed, 14 April 2021 09:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3054
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what does it have to do with Oracle? This is basic CSV rules - you need to enclose cell value in double quotes.

SY.
Re: how to remove html tags from the text [message #684173 is a reply to message #684171] Wed, 14 April 2021 23:16 Go to previous message
Sekhar6617
Messages: 10
Registered: March 2021
Junior Member
Hi Solomon,

Yes, this is something we can do with csv file.
However, below trick worked and removed all blank lines from the text.

REPLACE(To_char(REGEXP_REPLACE(NOTE_TEXT, '<[^>]+>|\&nbsp;|\n|\A','')),CHR(10),'')
Thank you.

Regards
Sekhar
Previous Topic: ORA-00904: "SUPPID": invalid identifier
Next Topic: Hierarchy again and again and again !
Goto Forum:
  


Current Time: Sat May 08 20:12:23 CDT 2021