Below is the function used to strip the HTML tags from a field in a SQL table.
ALTER FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('[if',@HTMLText)
SET @End = CHARINDEX('if]',@HTMLText, @Start)
SET @Length = (@End - @Start) + 1
WHILE ( @Start > 0 AND @End > 0 AND @Length > 0 )
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('[if',@HTMLText)
SET @End = CHARINDEX('if]',@HTMLText,@Start)
SET @Length = (@End - @Start) + 1
END
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText, @Start)
SET @Length = (@End - @Start) + 1
WHILE ( @Start > 0 AND @End > 0 AND @Length > 0 )
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,@Start)
SET @Length = (@End - @Start) + 1
END
set @HTMLText = REPLACE(@HTMLText, ' ', ' ');
while ( PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' +CHAR(13) +']%', @HTMLText COLLATE Latin1_General_100_BIN2 ) > 0)
begin
set @HTMLText = REPLACE(@HTMLText, SUBSTRING(@HTMLText, PATINDEX('%[' + CHAR(9)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255) + '-' + CHAR(13) +']%' , @HTMLText COLLATE Latin1_General_100_BIN2 ), 1 ), ' ')
end
RETURN LTRIM(RTRIM(@HTMLText))
END
GO
No comments:
Post a Comment