Solved

Sql remove html format

Posted on 2011-09-14
3
361 Views
Last Modified: 2012-05-12
I need to remove html format from text, for example bold, italcis etc. How do I do that in SQL 2008?
Or is it better to do it in C#?
0
Comment
Question by:johnkainn
3 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 83 total points
ID: 36534942
CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
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
RETURN LTRIM(RTRIM(@HTMLText))
END
GO


Test above function like this :

SELECT dbo.udf_StripHTML('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

Result Set:

UDF at SQLAuthority.com SQLAuthority.com

If you want to see this example in action click on Image. It will open large image.

refer
http://blog.sqlauthority.com/2007/06/16/sql-server-udf-user-defined-function-to-strip-html-parse-html-no-regular-expression/
0
 
LVL 9

Assisted Solution

by:mimran18
mimran18 earned 83 total points
ID: 36535108
Hi
   Here we go
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ccbde8aa-68da-44c0-b9b2-71bd66707eee/
 
Drop Function [dbo].[UDf_HTMLTags]
Go
CREATE Function [dbo].[UDf_HTMLTags]
    (@HTML varchar(Max))
    Returns varchar(Max)
As

Begin
    Declare @Start int,
        @End int,
        @Length int

    While CharIndex('<', @HTML) > 0 And CharIndex('>', @HTML, CharIndex('<', @HTML)) > 0
        Begin
        Select @Start = CharIndex('<', @HTML), 
          @End = CharIndex('>', @HTML, CharIndex('<', @HTML))
        Select @Length = (@End - @Start) + 1
        If @Length > 0
            Begin
            Select @HTML = Stuff(@HTML, @Start, @Length, '')
            End
        End

    return @HTML
End

Go
Select [dbo].[UDf_HTMLTags] ('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

Open in new window

0
 
LVL 7

Accepted Solution

by:
Kishan Zunjare earned 84 total points
ID: 36547171
Instead of removing HTML from sql you can remove HTML through C#

The solution is quite simple:

1. Retrieve all the HTML tags using this pattern: <(.|\n)*?>
2. Replace them with an empty string and return the result

Here's a C# function that does this:

private string StripHTML(string htmlString)
{
    //This pattern Matches everything found inside html tags;
    //(.|\n) - > Look for any character or a new line
    // *?  -> 0 or more occurences, and make a non-greedy search meaning
    string pattern = @"<(.|\n)*?>";
    return  Regex.Replace(htmlString,pattern,string.Empty);
}


Or with just one line of code:

string stripped = Regex.Replace(textBox1.Text,@"<(.|\n)*?>",string.Empty);

This is an simple and powerful solution.

Hope this will work
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now