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

False "LIKE" hits

Hi All... We have an application where a table has a field of type text that contains readable text, but some of it may have an HTML tag in it - specifically an HTML <img> tag. For example, it may be something like:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH.

The problem we're seeing is that when searching that field using LIKE, it's returning records that do in fact satisfy the SQL query, but we'd like it not to. That is, we'd like to exclude those HTML tags from the search.

For example:

SELECT ... WHERE TextField LIKE '%retr%'

is returning records that have those HTML tags. Yes, I know SQL is only doing its job... Anyone have any ideas to exclude those tags from the LIKE search? Thanks! -- Curt

Curt, I'm not clear on if you want to filter out the rows that have ANY html tags? or you're saying that part of a single column has a mixture of data AND html and you'd want to search only the non-html part of the column?

So, if you search this string below for the word "White" you want the row, but if you search on the word "Image" you do NOT want the row?

White Horse <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'>

so, you want to filter out everything between the "<" and ">" ?

You could write a function that parses each line, and searches for your string...

Bruce

|||

If I understand you correctly, you want the return to be:

BLAH BLAH BLAH ... BLAH BLAH BLAH

If that is a correct interpretation, it's not going to be either easy or pretty. By that I mean you will have to parse the data fields on the search which is really going to increase the time required to search. Indexes will not be used.

If this is the path you wish to take, you will need to create a User Defined Function that will take the entire field and strip out all characters between paired angle brackets.

|||

Thanks for the replies, Bruce and Arnie. I'm sorry for not making the issue very clear - believe it or not, it took me some time to figure out the wording I did manage to get down...

Using the statement SELECT * From TheTable WHERE TheField LIKE '%RETR%'

I would want the following record to be included:

BLAH RETR BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

But I would NOT want the following record included:

BLAH BLAH BLAH <img src='http://pics.10026.com/?src=RetrieveImage.aspx?ImageId=1234'> BLAH BLAH BLAH

In any records that are included, I would want the text returned as it appears - that is nothing filtered out.

You both pointed in the direction of writing a function to filter out that "<....>" data before applying a search. And yeah, that just adds to the overhead of the search... And quite frankly, I'm a bit nervous about that anyway - there's gonna be alot of these records and that LIKE just seems expensive. I've also been looking at indexing the text and using CONTAINS (that sound right?). We're also considering a sort of application-specific index of the text before we put it in the table as alot of the searchs are somewhat predictable.

|||

If there is only a single tag per row, you could have a WHERE clause that includes BOTH the substring that precedes "<" and the substring that follows ">". That should not require a function.

If there are multiple "<...>" entries per row, this method would not work as desired.

Dan

|||

IF, and that is a big IF in my opinion, the data is consistant and your sample correctly reflects the search value, this could work:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Comment varchar(max)
)

INSERT INTO @.MyTable VALUES ( 'BLAH RETR BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )
INSERT INTO @.MyTable VALUES ( 'BLAH BLAH BLAH <img src='http://pics.10026.com/?src='RetrieveImage.aspx?ImageId=1234''> BLAH BLAH BLAH' )

SELECT Comment
FROM @.MyTable
WHERE ( Comment LIKE '% RETR %'
AND Comment NOT LIKE '%=''RETR'
)

And of course, you could 'build up' the search values using parameters and constants.

This feels so 'unclean' that now I have to go take a shower...

|||

slightly shorter version.

Code Snippet

SELECT Comment
FROM @.MyTable
WHERE (Comment LIKE '%[ ]RETR[ ]%')

|||This problem was born for regular expressions:
http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx

you could also use charindex:

Code Snippet

SELECT *
From TheTable
where charindex('RETR', TheField)

not between charindex('<', TheField)

and charindex('>', TheField)

|||Spent far too long on this but here goes.

The following allows for any number of HTML Tags in the field.

Tested with the following:

Code Snippet

SELECT * into TheTable
From(
select 'BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH' as TheField
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH'
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH RETR BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAHRETRBLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
union all
select 'BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> BLAH BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> RETR BLAH BLAH BLAH BLAH <img src="RetrieveImage.aspx?ImageId=1234"> '
) as t1

create a table Numbers with a single field Num populated 1 to x where x = a number larger than the longest field you expect to work with:

Code Snippet

select * into numbers from (
select 1 as Num union all
select 2 as Num union all
select 3 as Num union all
...
select 2999 as Num union all
select 3000 as Num) as T1

Then use this query:

Code Snippet

select distinct TheField
from (
select
TheField,
case
when substring(TheField, num,1) = '>'

or num = 1

then substring(TheField, Num+1, charindex('<',TheField+'<', Num) - Num -1 )
else ''
end as c3
from TheTable, Numbers
) as T1
where c3 like '%RETR%'