Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Strip HTML Characters from a string column in SQL Server

In this puzzle we have a column called ht which includes HTML characters, we have strip those HTML characters. Please check the sample input and expected output.

Sample Input

ID ht
1 Pawan heelo I am ahere also
2 Pawan heelo
3 Ramesh Krishna

Expected Output

ID (No column name)
1 Pawan heelo I am ahere also
2 Pawan heelo
3 Ramesh Krishna

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE StripHTMLtags ( ID INT , ht VARCHAR(MAX) ) 
GO

INSERT INTO StripHTMLtags VALUES 
(1,'<b>Pawan </b> <a href="Rames">heelo</a> <html> I am ahere also </html>'),
(2,'<b>Pawan </b> <a href="Rames">heelo</a>'),
(3,'Ramesh Krishna')
GO

SELECT * FROM StripHTMLtags
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | USING XML Method

--

SELECT ID, CAST(ht AS XML).query('for $x in /. return ($x)//text()')
FROM StripHTMLtags

--

Solution – 2

--


SELECT ID, CONVERT( XML, ht ).value( '.', 'VARCHAR(MAX)' ) as ht
FROM StripHTMLtags

--

Output

--

/*------------------------
SELECT ID, CAST(ht AS XML).query('for $x in /. return ($x)//text()')
FROM StripHTMLtags
------------------------*/
ID          
----------- --------------------------------
1           Pawan heelo I am ahere also 
2           Pawan heelo
3           Ramesh Krishna

(3 row(s) affected)

                                            
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com