How to get rid of HTML tags and convert entities in SQL Server?

The best (ie non hacky) way to remove HTML tags is to load the text into a HTML parser and have it spit out the plain text version. There are plenty of HTML parsers out there, depending on your programming language, however, not SQL.

So in C# you'd read each entry in an external program, run it through the parser and write it back.

I'd suggest HtmlSanitiser to do this. Use it:

var sanitizer = new HtmlSanitizer(); PlainText = sanitizer.Sanitize(HtmlText);

the benefit of this is that you can remove specific tags from theHTML, leaving others. Eg you can leave i and b tags and remove everything else.

posted over 2 years ago

CC BY-SA 4.0

gbjbaanb‭

51 reputation 0 3 5 0

One way is to create a scalar function that takes an NVARCHAR(MAX) input and performs the following operations:

• replaces frequent tags with they plain text equivalent (e.g. <br> with chars 13 + 10)
• replaces spacial entities with their correspondent UTF character (e.g. © with ©)
• completely remove tags and content in some cases (e.g. <style>)

-- Credit: https://stackoverflow.com/a/39532235/2780791
CREATE OR ALTER FUNCTION [dbo].[StripHTML]
--by Patrick Honorez --- www.idevlop.com
--inspired by http://stackoverflow.com/questions/457701/best-way-to-strip-html-tags-from-a-string-in-sql-server/39253602#39253602
(
	@HTMLText nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
	DECLARE @Start  int
	DECLARE @End    int
	DECLARE @Length int

	set @HTMLText = replace(@htmlText, '<br>',CHAR(13) + CHAR(10))
	set @HTMLText = replace(@htmlText, '<br/>',CHAR(13) + CHAR(10))
	set @HTMLText = replace(@htmlText, '<br />',CHAR(13) + CHAR(10))
	set @HTMLText = replace(@htmlText, '<li>','- ')
	set @HTMLText = replace(@htmlText, '</li>',CHAR(13) + CHAR(10))

	set @HTMLText = replace(@htmlText, '&rsquo;' collate Latin1_General_CS_AS, ''''  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&quot;' collate Latin1_General_CS_AS, '"'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&amp;' collate Latin1_General_CS_AS, '&'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&euro;' collate Latin1_General_CS_AS, '€'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&lt;' collate Latin1_General_CS_AS, '<'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&gt;' collate Latin1_General_CS_AS, '>'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&oelig;' collate Latin1_General_CS_AS, 'oe'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&nbsp;' collate Latin1_General_CS_AS, ' '  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&copy;' collate Latin1_General_CS_AS, '©'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&laquo;' collate Latin1_General_CS_AS, '«'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&reg;' collate Latin1_General_CS_AS, '®'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&plusmn;' collate Latin1_General_CS_AS, '±'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&sup2;' collate Latin1_General_CS_AS, '²'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&sup3;' collate Latin1_General_CS_AS, '³'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&micro;' collate Latin1_General_CS_AS, 'µ'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&middot;' collate Latin1_General_CS_AS, '·'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ordm;' collate Latin1_General_CS_AS, 'º'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&raquo;' collate Latin1_General_CS_AS, '»'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&frac14;' collate Latin1_General_CS_AS, '¼'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&frac12;' collate Latin1_General_CS_AS, '½'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&frac34;' collate Latin1_General_CS_AS, '¾'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Aelig' collate Latin1_General_CS_AS, 'Æ'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Ccedil;' collate Latin1_General_CS_AS, 'Ç'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Egrave;' collate Latin1_General_CS_AS, 'È'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Eacute;' collate Latin1_General_CS_AS, 'É'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Ecirc;' collate Latin1_General_CS_AS, 'Ê'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&Ouml;' collate Latin1_General_CS_AS, 'Ö'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&agrave;' collate Latin1_General_CS_AS, 'à'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&acirc;' collate Latin1_General_CS_AS, 'â'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&auml;' collate Latin1_General_CS_AS, 'ä'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&aelig;' collate Latin1_General_CS_AS, 'æ'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ccedil;' collate Latin1_General_CS_AS, 'ç'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&egrave;' collate Latin1_General_CS_AS, 'è'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&eacute;' collate Latin1_General_CS_AS, 'é'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ecirc;' collate Latin1_General_CS_AS, 'ê'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&euml;' collate Latin1_General_CS_AS, 'ë'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&icirc;' collate Latin1_General_CS_AS, 'î'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ocirc;' collate Latin1_General_CS_AS, 'ô'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ouml;' collate Latin1_General_CS_AS, 'ö'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&divide;' collate Latin1_General_CS_AS, '÷'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&oslash;' collate Latin1_General_CS_AS, 'ø'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ugrave;' collate Latin1_General_CS_AS, 'ù'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&uacute;' collate Latin1_General_CS_AS, 'ú'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ucirc;' collate Latin1_General_CS_AS, 'û'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&uuml;' collate Latin1_General_CS_AS, 'ü'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&quot;' collate Latin1_General_CS_AS, '"'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&amp;' collate Latin1_General_CS_AS, '&'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&mdash;' collate Latin1_General_CS_AS, '—'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ndash;' collate Latin1_General_CS_AS, '–'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&bull;' collate Latin1_General_CS_AS, '•'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&trade;' collate Latin1_General_CS_AS, '™'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&rdquo;' collate Latin1_General_CS_AS, '"'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&lsquo;' collate Latin1_General_CS_AS, ''''  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&ldquo;' collate Latin1_General_CS_AS, ''''  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&oacute;' collate Latin1_General_CS_AS, 'ó'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&deg;' collate Latin1_General_CS_AS, '°'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&hellip;' collate Latin1_General_CS_AS, '…'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&atilde;' collate Latin1_General_CS_AS, 'ã'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&lrm;' collate Latin1_General_CS_AS, '•'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&iuml;' collate Latin1_General_CS_AS, 'ï'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&rarr;' collate Latin1_General_CS_AS, '→'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&bdquo;' collate Latin1_General_CS_AS, '"'  collate Latin1_General_CS_AS)

	-- Remove anything between <STYLE> tags
	SET @Start = CHARINDEX('<STYLE', @HTMLText)
	SET @End = CHARINDEX('</STYLE>', @HTMLText, CHARINDEX('<', @HTMLText)) + 7
	SET @Length = (@End - @Start) + 1

	WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
	SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
	SET @Start = CHARINDEX('<STYLE', @HTMLText)
	SET @End = CHARINDEX('</STYLE>', @HTMLText, CHARINDEX('</STYLE>', @HTMLText)) + 7
	SET @Length = (@End - @Start) + 1
	END

	-- Remove anything between <whatever> tags
	SET @Start = CHARINDEX('<', @HTMLText)
	SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
	SET @Length = (@End - @Start) + 1

	WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
	SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
	SET @Start = CHARINDEX('<', @HTMLText)
	SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
	SET @Length = (@End - @Start) + 1
	END

	set @HTMLText = replace(@htmlText, '&lsaquo;' collate Latin1_General_CS_AS, '<'  collate Latin1_General_CS_AS)
	set @HTMLText = replace(@htmlText, '&rsaquo;' collate Latin1_General_CS_AS, '>'  collate Latin1_General_CS_AS)

	RETURN LTRIM(RTRIM(@HTMLText))

END

posted over 2 years ago

CC BY-SA 4.0

Alexei‭

5003 reputation 112 101 690 487