Solved

Sql remove html format

Posted on 2011-09-14
3
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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