OraFAQ Forum: SQL & PL/SQL » how to remove html tags from the text

Home » SQL & PL/SQL » SQL & PL/SQL » how to remove html tags from the text

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to remove html tags from the text [message #684127]

Thu, 08 April 2021 07:39

Sekhar6617
Messages: 22
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

Report message to a moderator

Re: how to remove html tags from the text [message #684128 is a reply to message #684127]

Thu, 08 April 2021 08:05

Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just use REGEXP_REPLACE.

SY.

Report message to a moderator

Re: how to remove html tags from the text [message #684129 is a reply to message #684127]

Thu, 08 April 2021 08:22

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: how to remove html tags from the text [message #684132 is a reply to message #684128]

Thu, 08 April 2021 10:47

Sekhar6617
Messages: 22
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

Report message to a moderator

Re: how to remove html tags from the text [message #684135 is a reply to message #684132]

Thu, 08 April 2021 12:17

Solomon Yakobson
Messages: 3214
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.

Report message to a moderator

Re: how to remove html tags from the text [message #684152 is a reply to message #684135]

Fri, 09 April 2021 09:09

Sekhar6617
Messages: 22
Registered: March 2021

Junior Member

Hi Solomon,

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

Regards
Sekhar

Report message to a moderator

Re: how to remove html tags from the text [message #684170 is a reply to message #684152]

Wed, 14 April 2021 07:41

Sekhar6617
Messages: 22
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 369 times)

Report message to a moderator

Re: how to remove html tags from the text [message #684171 is a reply to message #684170]

Wed, 14 April 2021 09:16

Solomon Yakobson
Messages: 3214
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.

Report message to a moderator

Re: how to remove html tags from the text [message #684173 is a reply to message #684171]

Wed, 14 April 2021 23:16

Sekhar6617
Messages: 22
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

Report message to a moderator

Previous Topic:	ORA-00904: "SUPPID": invalid identifier
Next Topic:	Hierarchy again and again and again !

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 01:31:32 CDT 2023