Friday, July 26, 2013

SQL Server - How to remove HTML tags from data


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.

·         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
           
            

·         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
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

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.


/****** Object:  UserDefinedFunction [dbo].[usp_ClearHTMLTags]   ******/ 
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER 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),  
                    @End    = CHARINDEX('>', @String, CHARINDEX('<', @String)) 
            SELECT @Length = (@End - @Start) + 1 
             
            IF @Length > 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----------------------------------------------

18 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Vishal, Very good func... Thanks :)

    ReplyDelete
  3. Hi, how to solve when meeting with character:
    input:This an html test
    output:This ? an html test

    ReplyDelete
  4. Thank you very much for sharing this function, it was very useful.

    ReplyDelete
  5. Thank you for developing this. It works great!

    ReplyDelete
  6. This kind of lovely blog you’ve, glad I found it!?? new york web designs

    ReplyDelete
  7. Conveyancing… [...]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[...]… web design new york

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. certainly like your web-site but you have to check the spelling on quite a few of your posts. Many of them are rife with spelling problems and I in finding it very bothersome to inform the truth however I will definitely come again again. branding agencies in san francisco

    ReplyDelete
  10. Thrilled you desire sensible business online guidelines keep wearing starting tools suitable for the particular web-based business. cash ux designer san francisco

    ReplyDelete
  11. Please go on together with your wonderful weblog posts, I really like them. web design agencies los angeles

    ReplyDelete
  12. I dont think Ive caught all the angles of this subject the way youve pointed them out. Youre a true star, a rock star man. Youve got so much to say and know so much about the subject that I think you should just teach a class about it web designer la

    ReplyDelete
  13. Good website! I truly love how it is simple on my eyes and the data are well written. I am wondering how I could be notified whenever a new post has been made. I’ve subscribed to your feed which must do the trick! Have a nice day! web design agency

    ReplyDelete
  14. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. quickbooks data entry

    ReplyDelete
  15. Very useful.. About the 10th site I visited & most succinct fix. Thanks

    ReplyDelete
  16. That in that spot should reveal to you something. data entry assistant

    ReplyDelete
  17. A business can only be successful if the employees are efficient and productive. Data entry and outsource data entry to India processing has always offered various effective methods to businesses that in turn has made the tasks easier, quicker and even more efficient.

    ReplyDelete
  18. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work. receipt data entry

    ReplyDelete