Fetch text from HTML [URGENT]

Hello everybody,

I have data in my sql server table that is entered with a rich text editor so it contains html formatting. I need to show this data on the form with the formatting user has specified while entering the data. On the crystal report, I need to show the data not in the html format but simple text. I created a stored procedure to fetch the data and binded the report with the stored procedure. When data is printed on the report, it shows HTML tags with the data. Is there a way to fetch text in SQL so that report will only show text without HTML formatting?

Thanks.

It is stored a just a string of characters. It is retreived as just a string of characters. (Some of those characters are html coding characters.

You may wish to create a User Defined Function (UDF) that will strip out the html characters and can be used when necessary.

Here is an example of how you might create and use such a function. (It is incomplete -I'm sure you will be able to add the additional html coding...)

CREATE FUNCTION dbo.StripHTML
( @.StringIn varchar(500) )
RETURNS varchar(500)
AS
BEGIN
SET @.StringIn = replace( replace( @.StringIn, '

', '' ), '

', '' )
SET @.StringIn = replace( replace( @.StringIn, '<b>', '' ), '</b>', '' )
-- add addition html codes here
RETURN ( @.StringIn )
END
GO

SELECT dbo.StripHTML('

This is a <b>Test</b>

')

|||

I was looking for a quicker and easier way to do it (if there is something). As you said, I am writing my own UDF to parse HTML.

Thanks a lot.

|||

You can use the following code to replace all the HTML tags....

CREATE Function dbo.Html2Text(@.HtmlString As Varchar(8000))
Returns Varchar(8000)
as
Begin
Declare @.TagString As Varchar(8000)
Declare @.TagStart As int
Declare @.TagEnd As int

Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
While @.TagStart <> 0 And @.TagEnd <> 0 And @.TagEnd > @.TagStart
Begin
Select @.TagString = Substring(@.HtmlString, @.TagStart, @.TagEnd - @.TagStart + 1)
Select @.HtmlString = Replace(@.HtmlString, @.TagString, '')
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
End

Select @.HtmlString = Replace(@.HtmlString, '&nbsp;', ' ')
Select @.HtmlString = Replace(@.HtmlString, '&amp;', '&')
Select @.HtmlString = Replace(@.HtmlString, '&quot;', '''')
Select @.HtmlString = Replace(@.HtmlString, '&#', '#')
Select @.HtmlString = Replace(@.HtmlString, '&lt;', '<')
Select @.HtmlString = Replace(@.HtmlString, '&gt;', '>')
Select @.HtmlString = Replace(@.HtmlString, '%20', ' ')
Select @.HtmlString = Replace(@.HtmlString, Char(10), '')
Select @.HtmlString = Replace(@.HtmlString, Char(13), '')
Select @.HtmlString = LTrim(RTrim(@.HtmlString))

Return @.HtmlString
End

Go

Select dbo.Html2Text('

Test&nbsp;Data

'); -- Result :Test Data