CODEWITHSUNDEEP
sqlsql-server
Nisar
Nisar

Reputation: 6038

Convert Plain Text to HTML

I am having HTML code text as

select prd_desc from prd_mst

prd_desc
------------------------------------------------
<u>hello</u> <font color="#cc0000">dfgfdg</font>

How can i make it format in a HTML as 'hello...'

I am using replace function for all those special HTML characters or sqlserver has some shortcut, function for it.

Upvotes: 1

Views: 9086

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use the XML datatype for this.

If you cast your value to XML that will give you a XML fragment that consists of one text element only. Extract that value using the value() function and you have a string that is not entitized.

select cast(p.prd_desc as xml).value('text()[1]', 'nvarchar(max)') as prd_desc
from dbo.prd_mst as p

Result:

prd_desc
-------------------------------------------------
<u>hello</u> <font color="#cc0000">dfgfdg</font>

Upvotes: 3

user3673263
user3673263

Reputation:

Try this,

create  FUNCTION udf_HtmlDecode  
(  
    @UnDecoded as varchar(max)  
)  
RETURNS varchar(500)  
AS  
BEGIN  
--declare @UnDecoded as varchar(500);  
--set @UnDecoded = 'xvfxv &lt;u&gt;cbgdfgd&lt;/u&gt; &lt;font color=&quot;#cc0000&quot;&gt;dfgfdg&lt;/font&gt; &lt;b&gt;gdfgdf&lt;/b&gt;&lt;br&gt;';  
  DECLARE @Encoded as varchar(max)  

  --order is important here. Replace the amp first, then the lt and gt.   
  --otherwise the &lt will become &amp;lt;   
  SELECT @Encoded = Replace(  
  Replace(  
    Replace(  
      Replace(@UnDecoded,'&amp;','&'),  
    '&lt;', '<'),  
  '&gt;', '>'),  
'&quot;','"')    
 RETURN @Encoded  
--select @Encoded  
END

then execute

select dbo.udf_HtmlDecode('&lt;u&gt;hello&lt;/u&gt; &lt;font color=&quot;#cc0000&quot;&gt;dfgfdg&lt;/font&gt;')

will return

<u>hello</u> <font color="#cc0000">dfgfdg</font>

Upvotes: 1

Related Questions