Solved

Sql remove html format

Posted on 2011-09-14
3
370 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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