1. Background
The purpose of this article
is to provide a way of cleaning up of HTML tags within the data. When we use various styles or tabular format data in UI
using Rich Text Editor/ Rad Grid etc, it will save data in database with HTML
tags.
is to provide a way of cleaning up of HTML tags within the data. When we use various styles or tabular format data in UI
using Rich Text Editor/ Rad Grid etc, it will save data in database with HTML
tags.
·
Fig. 1 showing data with style using Rich text editor in UI
Fig. 1 showing data with style using Rich text editor in UI
·
Fig 2 showing data saved in table corresponding to fig 1 with HTML tags
Fig 2 showing data saved in table corresponding to fig 1 with HTML tags
·
If we simply pull the data from the table for SSRS or excel then data
will come with HTML tags unless we explicitly remove the HTML tags from the
data.
If we simply pull the data from the table for SSRS or excel then data
will come with HTML tags unless we explicitly remove the HTML tags from the
data.
2.
What
is HTML Tags?
An HTML element starts with a start tag (<p>) and ends with end
tag (<p/>) and everything between
tag (<p/>) and everything between
Start tag and End tag is HTML element. e.g.
<b>Following are the popular
databases: <br /> </b>1. SQL
Server <br /> 2. Oracle <br
/> 3.
Teradata <br /> 4. Sybase
databases: <br /> </b>1. SQL
Server <br /> 2. Oracle <br
/> 3.
Teradata <br /> 4. Sybase
3.
Script
to remove HTML tags from the data?
We are using SQL Server CHARINDEX function to get the
positions of ‘<’ and ‘>’ and once found replacing the string between <….> with blank using STUFF
function. We are using WHILE Loop that will run till presence of ‘<’ & ‘>’
in the string. Below is the UDF script that performs HTML Tags clean up from data.
positions of ‘<’ and ‘>’ and once found replacing the string between <….> with blank using STUFF
function. We are using WHILE Loop that will run till presence of ‘<’ & ‘>’
in the string. Below is the UDF script that performs HTML Tags clean up from data.
/****** Object: UserDefinedFunction
[dbo].[usp_ClearHTMLTags] ******/
[dbo].[usp_ClearHTMLTags] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
ON
GO
/****************************************************************************
Name of Author : Vishal Jharwade
Purpose : The Purpose of this function is to clean the
html tags from the data.
***************************************************************************************/
CREATE FUNCTION [dbo].[usp_ClearHTMLTags]
(@String NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT,
@End INT,
@Length INT
WHILE CHARINDEX(‘<‘, @String) > 0 AND CHARINDEX(‘>’, @String, CHARINDEX(‘<‘, @String)) > 0
BEGIN
SELECT
@Start =
CHARINDEX(‘<‘, @String),
@Start =
CHARINDEX(‘<‘, @String),
@End =
CHARINDEX(‘>’, @String, CHARINDEX(‘<‘, @String))
SELECT @Length = (@End – @Start) + 1
IF @Length >
0
0
BEGIN
SELECT @String = STUFF(@String, @Start, @Length, ”)
END
END
RETURN @String
END
4. Conclusion
By using above UDF, We can clean the
HTML tags from the data.
———————————–End
of Article———————————————-