Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Wednesday, September 10, 2014

Replace HTML Tags from SQL Text Output


We may come across some text fields in SQL table having values like '..xyz<p>asdff...', in that case we need to replace HTML tags.

To achieve that create below function and use them in SQL procedures:

CREATE FUNCTION [dbo].[RemoveHTMLTag] ( @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 REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@HTMLText)), '$', '. '), '£', '. '), '€', '. ')
   END

In Procedure:

SELECT dbo.RemoveHTMLTag(ColumnName) FROM Table

No comments:

Post a Comment