Strip HTML tags from field value

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)

Strip HTML tags from field value

Author

Topic

jns
Starting Member

19 Posts

Posted - 2002-02-22 : 07:48:35

How can I strip all the HTML tags from a varchar field before returning it in my selection? I can replace the < or the > with "" but I want to remove everything in between as well. Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-22 : 07:52:27

This is best done on the web server side, before the data is INSERTed into SQL Server, using regular expressions:

http://www.4guysfromrolla.com/demos/StripHTML1.asp
http://www.4guysfromrolla.com/demos/StripHTML2.asp

jns
Starting Member

19 Posts

Posted - 2002-02-22 : 13:47:13

True, but unfortunately I have a need to keep it to display later. I have a need to do a javascript popup with with the details but I need to strip the HTML out to use it in a popup.

Does anyone know the way to use a regular expression REPLACE function in an SQL statement?

quote:

This is best done on the web server side, before the data is INSERTed into SQL Server, using regular expressions:

http://www.4guysfromrolla.com/demos/StripHTML1.asp
http://www.4guysfromrolla.com/demos/StripHTML2.asp

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-22 : 16:00:32

you can use this UDF if you're on SQL Server 2000, or
create a stored proc is you have 7.0

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
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 CHARINDEX('<', @myNewDesc) > 0
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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

helena

jns
Starting Member

19 Posts

Posted - 2002-02-25 : 08:51:24

Worked perfectly, thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources