SQL Server 2000 - Selecting From A Field That Contains HTML

Hello,
I have got a field in my database that has HTML code. I need to pass a parameter of a keyword to my SP in order to find this keyword in my field "LIKE '%' + @Keyword + '%". Is there a way to ignore those HTML tags while searching?
feesuAsked:
Who is Participating?
 
BadKarmaConnect With a Mentor Commented:
You could create a procedure that strips HTML from results, which would also be useful for other things.

One I use is:

USE [mediadienstencentrumdb1]
GO
/****** Object:  UserDefinedFunction [dbo].[RemoveHTML]    Script Date: 11/20/2007 11:11:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE function [dbo].[RemoveHTML] (@intro varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
            declare @leftbracket char(1)
            declare @rightbracket char(1)
            declare @resultstring varchar(4000)
            declare @remainingString varchar(4000)
            declare @openbracketposition int
            declare @closebracketposition int
            set @leftbracket = '<'
            set @rightbracket = '>'
            set @resultstring = ''
            set @remainingString = @intro
            while len(@remainingString) > 0
            begin
                        set @openbracketposition = charindex(@leftbracket, @remainingString)
                        if @openbracketposition > 0
                        begin
                                    set @resultstring = @resultstring + left(@remainingString, @openbracketposition - 1)
                                    set @remainingString = substring(@remainingString, @openbracketposition, 3000)
                                    set @closebracketposition = charindex(@rightbracket, @remainingString)
                                    if @closebracketposition > 0
                                                set @remainingString = substring(@remainingString, @closebracketposition + 1, 3000)
                                    else
                                                set @remainingString = ''
                        end
                        else
                        begin
                                    set @resultstring = @resultstring + @remainingString
                        	    set @remainingString = ''
                        end
            end
RETURN @resultstring
END

Open in new window

0
 
BadKarmaCommented:
Oh, and ignore that first USE. It's one db where I use the function.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
imitchieConnect With a Mentor Commented:
go to the first comment's link - accepted solution has a much better UDF.

dbo.ufn_removetags(fieldname) LIKE '%' + @Keyword + '%"
0
 
feesuAuthor Commented:
I tried creating the function from the first link, but got the following errors:

drop function dbo.UDF_RemoveTags
do
Create function UDF_RemoveTags(@inStr varchar(8000)) returns varchar(8000) as
begin
declare @pos int
set @pos = CHARINDEX('<', @inStr) * sign(charindex('>', @instr))
WHILE @pos > 0
Select @inStr = STUFF(@inStr, @pos, CHARINDEX('>', @inStr) - @pos + 1, ' ') ,@pos = CHARINDEX('<', @inStr) * sign(charindex('>', @instr))
return @inStr
end
go



Error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'do'.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@inStr'.
Server: Msg 137, Level 15, State 1, Line 8
Must declare the variable '@inStr'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@inStr'.
0
 
imitchieCommented:
change the DO to GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.