Friday, January 25, 2013

SQL Function to Strip HTML String

I've found this simple SQL script that create a SQL Function that receive an HTML varchar(max) string in input and return a linear flat Text String (varchar(max)) without HTML markup.
This can be usefull if you plan to store HTML in SQL column and you need to return a plain text.

CREATE FUNCTION [dbo].[StripHTML]
 (@HTMLText VARCHAR(MAX))
 RETURNS VARCHAR(MAX)
 AS
 BEGIN
 DECLARE @Start INT
 DECLARE @End INT
 DECLARE @Length INT
 SET @Start = CHARINDEX('<',@HTMLText)
 SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
 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,CHARINDEX('<',@HTMLText))
  SET @Length = (@End - @Start) + 1
 END
 RETURN LTRIM(RTRIM(@HTMLText))
 END

This just for my note and reminder.

No comments: