Removing HTML in Sql Server 2000

Discussion in 'Programming' started by directorycollector, Oct 16, 2006.

0
  1. #1
    Hi, I return large text fields from my database and I currently strip the HTML out at the client and only show the first 200 characters of the column. I would like to start doing that on the server so that I don't have to pass so much information back.

    Does anyone have a function for removing HTML from a text column in SQL Server 2000?

    It would be much appreciated.

    Thanks,
    George
     
    directorycollector, Oct 16, 2006 IP
  2. directorycollector

    directorycollector Peon

    Messages:
    259
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Can anyone add improvements or corrections (if they see a problem), it works ok for my requirements.

    CREATE FUNCTION fnRemoveHTML (
	@String			varchar(8000)
) RETURNS varchar(8000)
AS
BEGIN
	-- Declare variables here
	DECLARE	@Start		int,
		@End		int

	-- Add the T-SQL statements to compute the return value here
	WHILE	PATINDEX('%<[^>]%>%', @String) > 0
		BEGIN
		SET	@Start = PATINDEX('%<[^>]%>%', @String)
		SET	@End = @Start + PATINDEX('%>%', RIGHT(@String, LEN(@String) - @Start))
		SET	@String = LEFT(@String, @Start - 1) + RIGHT(@String, LEN(@String) - @End)
		END

	-- Return the result of the function
	RETURN @String
END
GO

SELECT	dbo.fnRemoveHTML('This <strong>string</string> has <a href="http://www.html.com/">HTML</a> in it.')
    Code (markup):
     
    directorycollector, Oct 16, 2006 IP