?
Solved

SQL Server 2000 - Selecting From A Field That Contains HTML

Posted on 2007-11-20
6
Medium Priority
?
205 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
[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
6 Comments
 
LVL 31

Accepted Solution

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

Assisted Solution

by:BadKarma
BadKarma earned 100 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 200 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

800 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