how to strip all html tags and special characters from string using sql server

Question

I work as asp.net developer using C#, I receive text like this from the client:

> &lt;p&gt;&lt;a
> href=&quot;http://www.vogue.co.uk/person/kate-winslet&quot;&gt;KATE
> WINSLET&lt;/a&gt;&amp;nbsp;has given birth to a 9lb baby boy. The
> Oscar-winning actress welcomed the baby with her husband Ned Rocknroll
> at a hospital in Sussex.&lt;/p&gt;
> 
> &lt;p&gt;&amp;quot;Kate had &amp;#39;Baby Boy Winslet&amp;#39; on
> Saturday at an NHS Hospital,&amp;quot; Winslet&amp;#39;s spokeswoman
> said, adding that the family were &amp;quot;thrilled to
> bits&amp;quot;.&lt;/p&gt;
> 
> &lt;p&gt;The announcement suggests that the child might bear his
> mother&amp;#39;s surname, rather than his father&amp;#39;s slightly
> more unusual moniker.&lt;/p&gt;
> 
> &lt;p&gt;The baby is Winslet&amp;#39;s third - she is already mother
> to Mia, 13, and Joe, eight, &amp;nbsp;from previous relationships -
> and her husband&amp;#39;s first. They met on Necker Island, owned by
> Rocknroll&amp;#39;s uncle, Richard Branson, and&lt;a
> href=&quot;http://www.vogue.co.uk/news/2013/kate-winslet-married-to-ned-rocknroller---wedding-details&quot;&gt;married almost a year ago&lt;/a&gt;&amp;nbsp;in New York.&lt;/p&gt;

I need a way to extract the real text without tags and special characters using sql server 2008 or above ??

Are you constrained to use SQL for this? It would probably be more suited to be handled in the application. — bendataclear
– bendataclear, Commented Dec 11, 2013 at 10:12
@bendataclear If there are a way to handle it in the application layer, I would like to use it.. — Dot Freelancer
– Dot Freelancer, Commented Dec 11, 2013 at 10:14
@bendataclear I think you are being too defensive. SQL is unsuitable for this, it must be done in the application layer. — Twinkles
– Twinkles, Commented Dec 11, 2013 at 10:48

gbn · Accepted Answer · 2013-12-11 11:29:24Z

1

The best I can suggest is to use a .net HTML parser or such which is wrapped in a SQL CLR function. Or to wrap the regex in SQL CLR if you want.

Note regex limitations: http://www.codinghorror.com/blog/2008/06/regular-expressions-now-you-have-two-problems.html

Raw SQL language won't do it: it is not a string (or HTML) processing language

answered Dec 11, 2013 at 11:29

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Twinkles Over a year ago

Since he is developing in C# there is no need for a CLR wrapper.

Community · Accepted Answer · 2016-10-21 20:26:02Z

I recently had the same requirement (to remove HTML tags and entities) so developed this function in SQL Server.

CREATE FUNCTION CTU_FN_StripHTML (@dirtyText NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
-- Cleaned Text
DECLARE @cleanText NVARCHAR(MAX)=RTRIM(LTRIM(@dirtyText));
-- HTML Tags
DECLARE @tagStart SMALLINT =PATINDEX('%<%>%', @cleanText);
DECLARE @tagEnd SMALLINT;
DECLARE @tagLength SMALLINT;
-- HTML Entities
DECLARE @entityStart SMALLINT =PATINDEX('%&%;%', @cleanText);
DECLARE @entityEnd SMALLINT;
DECLARE @entityLength SMALLINT;
WHILE @tagStart > 0
    OR 
    @entityStart > 0
BEGIN
-- Remove HTML Tag 
SET @tagStart=PATINDEX('%<%>%', @cleanText);
IF @tagStart > 0 
BEGIN
SET @tagEnd=CHARINDEX('>', @cleanText, @tagStart);
SET @tagLength=(@tagEnd - @tagStart) + 1;
SET @cleanText=STUFF(@cleanText, @tagStart, @tagLength, '');
END;
-- Remove HTML Entity
SET @entityStart=PATINDEX('%&%;%', @cleanText);
IF @entityStart > 0 
BEGIN
SET @entityEnd=CHARINDEX(';', @cleanText, @entityStart);
SET @entityLength=(@entityEnd - @entityStart) + 1;
SET @cleanText=STUFF(@cleanText, @entityStart, @entityLength, '');
END;
END;

SET @cleanText = RTRIM(LTRIM(@cleanText))
RETURN @cleanText;
END;

Community · Accepted Answer · 2017-05-23 12:06:45Z

0

HTML is so complex it's a very bad idea to do this without an HTML Parser.

You might be interested in This Question. The answer that's accepted there is to just use Lynx via the command line and dump the output to a file. If you can do it outside the users page-load it might be the best option.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Dec 11, 2013 at 10:20

bendataclear

3,8574 gold badges34 silver badges58 bronze badges

5 Comments

Dot Freelancer Over a year ago

umm, I think I need simpler solution

bendataclear Over a year ago

@dotWasim The problem is, it will always be a sliding scale between simple and reliable, there won't be a simple solution that can take everything into account. You can make a basic function to remove everything between tags but this won't take into account the codes.

Dot Freelancer Over a year ago

thanks, I don't need any code inside the tags.. anyway I coded it using regix for now

bendataclear Over a year ago

@dotWasim I am reminded of this answer: stackoverflow.com/a/1732454/1281901

Dot Freelancer Over a year ago

@bendataclear I just take first 400 characters from the text to display it as sample and then uses can click read more button to read the whole news.. so what I need is simple and I don't need to make it complex..

Collectives™ on Stack Overflow

how to strip all html tags and special characters from string using sql server

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related