Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Stripping away HTML tags in sql server 7.0

Author  Topic  fizgig Starting Member 34 Posts Posted - 2002-01-18 : 06:27:20 Below is my stored procedure, i want to strip all html tags from the 'tekst' column before returning the recordset. Someone gave me a great function (see below the the stored procedure), but i've read that sql server 7.0 doesn't support user functions. Can anyone give me a hint how to 'merge' the function with the stored procedure? I'm so newbie ;-) CREATE PROCEDURE sp_pagedSearch ( @Page int, @RecsPerPage int, @Forum int, @Zoekopdracht varchar (50) ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATE TABLE #TempPagedSearch ( [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [bericht_id] [numeric](18, 0) NULL, [start_bericht] [numeric](18, 0) NULL, [KEY] [numeric] (18,0) NULL , [RANK] [int] NULL , [naam] [varchar] (50) NULL , [verstuurd] [datetime] NULL , [onderwerp] [varchar] (50) NULL , [tekst][text]NULL ) -- Insert the rows from tblItems into the temp. table INSERT INTO #TempPagedSearch (bericht_id, start_bericht, [KEY], RANK, naam, verstuurd, onderwerp, tekst) Select id, start_bericht, [KEY], RANK, naam, verstuurd, onderwerp, tekst FROM ContainsTable(bericht, *, @Zoekopdracht ) S, bericht B WHERE B.id = S.[KEY] AND verwijderd <> 1 AND Forum = @Forum ORDER BY Rank Desc -- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempPagedSearch TI WHERE TI.ID >= @LastRec ) , Totaal = (Select count(*) FROM #TempPagedSearch TI) , Eerste = (@FirstRec + 1), Laatste = (@LastRec - 1) FROM #TempPagedSearch WHERE ID > @FirstRec AND ID < @LastRec -- Turn NOCOUNT back OFF SET NOCOUNT OFF GO --------------- CREATE FUNCTION cleanLongDesc(@myLongDesc varchar(8000)) RETURNS varchar(8000) -- cleaned long desc AS BEGIN DECLARE @fpos int, @spos int, @myNewDesc varchar(8000) SET @myNewDesc=@myLongDesc IF CHARINDEX('<', @myLongDesc) > 0 BEGIN SET @fpos=CHARINDEX('<', @myNewDesc) WHILE @fpos > 0 BEGIN SET @spos=CHARINDEX('>', @myNewDesc) IF @spos > @fpos SET @myNewDesc=STUFF(@myNewDesc, @fpos, @spos-@fpos+1, '') ELSE SET @myNewDesc=STUFF(@myNewDesc, @spos, 1, '') SET @fpos=CHARINDEX('<', @myNewDesc) END END RETURN @myNewDesc END