How to Remove HTML Tags from String in SQL Server

narasiman rao

8y14.6k

i have created one function as follows

CREATE FUNCTION [dbo].[fn_parsehtml]

(

@htmldesc varchar(max)

)

returns varchar(max)

begin

declare @first int, @last int,@len int

set @first = CHARINDEX('<',@htmldesc)

set @last = CHARINDEX('>',@htmldesc,CHARINDEX('<',@htmldesc))

set @len = (@last - @first) + 1

while @first > 0 AND @last > 0 AND @len > 0

begin

---Stuff function is used to insert string at given position and delete number of characters specified from original string

set @htmldesc = STUFF(@htmldesc,@first,@len,'')

SET @first = CHARINDEX('<',@htmldesc)

set @last = CHARINDEX('>',@htmldesc,CHARINDEX('<',@htmldesc))

set @len = (@last - @first) + 1

end

return LTRIM(RTRIM(@htmldesc))

end

select dbo.fn_parsehtml('

MRD No: 6777	InPatient Number: IP/150402/00009	Patient Name: MR. BALASUBRAMANIAN K	Gender: Male	Age: 69 Year(s)
Consultant: Dr.SUDHIR R R

Regional Anaesthesia

REGIONAL ANAESTHESIA
Weight		kg	ASA grade	II

Regional Anaesthesia

Satisfactory

Technique

Peribulbar

Drugs

Xylocaine	2	%	4	ml(...mgs)
Marcaine	0.5	%	4	ml(...mgs)

once i execute the above query output i get no problem

but like this i have multiple html tags.

how to execute multiple html tags at single select query.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

kamal k6y ago
This code will work for them who do NOT want to use UDF (User Defined Function).

Assuming that one of your sql table col. contains HTML tags like below and you want to remove everything between < and > .

[Remark] field contains HTML tags like: < div > < p >< span > We are trying to contact the company (Tel: < /span >< span> < /p > < /div > etc'

Step#1: Store your result set to a Temp table

select .... into #t1 from....where...

Step#2: Use the following code to remove everything between < and > and update the field.

WHILE @@ROWCOUNT > 0 BEGIN UPDATE #t1 SET [Remark] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(STUFF([Remark],CHARINDEX('<',[Remark]),CHARINDEX('>',[Remark]) - CHARINDEX('<',[Remark]) + 1,'') )), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' '), ' ', ''))) WHERE [Remark] LIKE '%<%>%' END

Step#3: Select your desired result from Temp table

SELECT * FROM #t1
0
Altaf Ansari8y ago
Please check..
DECLARE @x NVARCHAR(MAX) = '
My text.
More text.
'

SELECT x.value('.', 'NVARCHAR(MAX)')
FROM (
    SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), ', '<') AS XML)
) r
0

narasiman rao8y ago

i have write the function to parse xml and metnion that function in below select query

first html tag as follows

select dbo.fn_parsehtml('

MRD No: 6777	InPatient Number: IP/150402/00009	Patient Name: MR. BALASUBRAMANIAN K	Gender: Male	Age: 69 Year(s)
Consultant: Dr.SUDHIR R R

Regional Anaesthesia

REGIONAL ANAESTHESIA
Weight		kg	ASA grade	II

Regional Anaesthesia

Satisfactory

Technique

Peribulbar

Drugs

Xylocaine	2	%	4	ml(...mgs)
Marcaine	0.5	%	4	ml(...mgs)

second html tag as follows

Medical Record No. : 1187152

Patient Name : MR. RENGASAMY S

Age : 78 Year(s)

Sex : Female

Address : C 233, T N H B COLONY, , TAMBARAM SANATORIUM, CHENNAI,

TAMIL NADU, INDIA - 600047.

Report Date : 2015-04-01	Optometrist :	Start Time :
Consultant : Dr.VEENA BASKARAN

FollowUp

FOLLOW-UP
		Instructions
Action Plan	* OU Findings noted * OU Condition explained * Need for balancing for myopic power explained * Patient to decide * OD PE+IOL under LA under GVP * GVP explained clearly * Followed by OS

Nutritional Screening

Need special dietary requirements: Hence referred to a dietican outside SN

Examined by Consultant: Dr.VEENA BASKARAN Time: 01-04-2015 11:30:01

how to execute that two html tags at single select query.

this is the information i provided

Laxmidhar Sahoo8y ago
sir

for you cal call the function fn_parsehtml multiple times in a single query and executed
many times
0
Ram Nunna8y ago
Hi Narasiman,

could please provide some information.

Thanks,
Ram
0