User defined function to strip html in sql server 2005/2008
June 9, 2010 at 10:44 am Leave a comment
I need to strip html at many places in my project, I can do that by JavaScript function but I want some more common mechanism to strip html codes while getting the data from database. So I thought of using striping html @ database level and I searched on the net and here is the function for striping html.
CREATE FUNCTION [dbo].[udf_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
GO
Above function takes string as a parameter which contains html data and it will return string without html part.
Example:
Select dbo.udf_StripHTML('<br> This is <hr> <html> Pranav''s Blog </html>') and it will return below string
-> This is Pranav's Blog
Happy Programming 
Entry filed under: SQL Server. Tags: strip html in sql server 2005, strip html in sql server 2008, user defined function for strip html.
Trackback this post | Subscribe to the comments via RSS Feed