Solved

SQL Server 2000 - Selecting From A Field That Contains HTML

Posted on 2007-11-20
6
200 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:feesu
6 Comments
 
LVL 31

Accepted Solution

by:
James Murrell earned 50 total points
ID: 20318608
0
 

Assisted Solution

by:BadKarma
BadKarma earned 25 total points
ID: 20318609
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
 

Expert Comment

by:BadKarma
ID: 20318615
Oh, and ignore that first USE. It's one db where I use the function.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 50 total points
ID: 20333091
go to the first comment's link - accepted solution has a much better UDF.

dbo.ufn_removetags(fieldname) LIKE '%' + @Keyword + '%"
0
 

Author Comment

by:feesu
ID: 20402255
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20402265
change the DO to GO
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

943 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now