Solved

SQL Server 2000 - Selecting From A Field That Contains HTML

Posted on 2007-11-20
6
199 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

22 Experts available now in Live!

Get 1:1 Help Now