Create Function [DBO]. [clearhtml] (@ MACO varchar (8000 ))
Returns varchar (8000) as begin
Declare @ I int
While 1 = 1
Begin
Set @ I = Len (@ MACO)
Set @ MACO = Replace (@ MACO, substring (@ MACO, charindex ('<', @ MACO ),
Charindex ('>', @ MACO)-charindex ('<', @ MACO) + 1), Space (0 ))
If @ I = Len (@ MACO)
Break
End
Set @ MACO = Replace (@ MACO ,'','')
Set @ MACO = Replace (@ MACO, '& nbsp ;','')
Set @ MACO = ltrim (rtrim (@ MACO ))
Set @ MACO = Replace (@ MACO, char (9 ),'')
Set @ MACO = Replace (@ MACO, char (10 ),'')
Set @ MACO = Replace (@ MACO, char (13 ),'')
Return (@ MACO)
End
-- 2. test example
Declare @ mark varchar (8000)
set @ mark = '
<Table cellpadding = 0 cellspacing = 0 style = "margin-left: 15px"> <tr valign = top> <TD style = "height: 62px; padding-left: 92px "nowrap> <Div style =" position: relative "> <form name = f action =/S> <input type = text name = WD id = Kw size = 42 maxlength = 100> <input type = submit value = Baidu id = Sb> <Div id = sug onselectstart = "Return false"> </div> <span id = HP> <a href =/search/jiqiao.html> help </a> <br> <a href =/gaoji/advanced.html> advanced </a> </span> </form> </div> </TD> </tr> </table>
</Body>'
Select DBO. clearhtml (@ mark)
-- 3. Running result
/*
New
---------------------------------------
Log on to the news page, paste it, and learn about the MP3 image and video help advanced.
*/
/*
However, the above function still has a problem. If there is a mark like "" "or" <> "in the content, it cannot meet our requirements.
*/
-- Enhanced Edition
Create Function [DBO]. [clearhtml_v2] (@ MACO varchar (8000 ))
Returns varchar (8000)
As
Begin
Declare @ randchar_one nvarchar (200)
Declare @ randchar_two nvarchar (200)
If (charindex ('<', @ MACO)> 0)
Begin
Set @ randchar_one = 'd4678b36-B958-4274-B81E-BBA636CFB427 ';
Set @ randchar_two = '49e374cc-9e1a-4850-897c-27074de32e7f ';
Set @ MACO = Replace (@ MACO, '<', @ randchar_one)
Set @ MACO = Replace (@ MACO, '>', @ randchar_two)
End
Declare @ I int
While 1 = 1
Begin
Set @ I = Len (@ MACO)
Set @ MACO = Replace (@ MACO, substring (@ MACO, charindex ('<', @ MACO ),
Charindex ('>', @ MACO)-charindex ('<', @ MACO) + 1), Space (0 ))
If @ I = Len (@ MACO)
Break
End
Set @ MACO = Replace (@ MACO ,'','')
Set @ MACO = Replace (@ MACO, '& nbsp ;','')
Set @ MACO = ltrim (rtrim (@ MACO ))
Set @ MACO = Replace (@ MACO, char (9 ),'')
Set @ MACO = Replace (@ MACO, char (10 ),'')
Set @ MACO = Replace (@ MACO, char (13 ),'')
If (charindex (@ randchar_one, @ MACO)> 0)
Begin
Set @ MACO = Replace (@ MACO, 'd4678b36-B958-4274-B81E-BBA636CFB427 ',' <')
Set @ MACO = Replace (@ MACO, '49e374cc-9e1a-4850-897c-27074de32e7f', '> ')
End
Return (@ MACO)
End
Select DBO. clearhtml_v2 ('<p> AAAA </P> <materia medica> <a href = "www.baidu.com"/> ')
-- Running result:
/*
Aaaa <materia medica Outline>
*/