Conocimiento intermedio de T -SQL
permisos adecuados para crear una función de base de datos objeto
Mostrar más instrucciones
1
Conectarse a la base de datos , y . crear un nuevo archivo de SQL
2
Escriba el siguiente código SQL :
CREATE FUNCTION [ dbo ] [ CleanHTML ]
(
@ . DirtyText varchar (MAX )
)
RETURNS VARCHAR (MAX )
AS
COMENZAR
DECLARE @ BeginPos int
DECLARE @ endpos int
DECLARE @ Len int
- Sustituir la entidad HTML y con el carácter '&' (esto se debe hacer en primer lugar, como
< p > - ' &' podría ser el doble codifica como ' &' )
SET @ BeginPos = CHARINDEX ( '& ', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) COMENZAR
SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length , ' &' )
SET @ BeginPos = CHARINDEX ( '& ', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
End of
- Sustituir la entidad HTML < con el carácter ' <'
SET @ BeginPos = CHARINDEX (' <' , @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ len = (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) comienzan
SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length , '< ; ' )
SET @ BeginPos = CHARINDEX (' <' , @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ Len = ( @ endpos - @ BeginPos ) + 1
End of
- Reemplace el código HTML entidad> con el carácter '>'
SET @ BeginPos = CHARINDEX ( '>' , @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 Y @ endpos > 0 AND @ Len > 0 ) comienzan
SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length , '> ' )
SET @ BeginPos = CHARINDEX ( '>' , @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ len = (@ endpos - @ BeginPos ) + 1
END
- Sustituir la entidad HTML y con el carácter '&'
SET @ BeginPos = CHARINDEX ( '& ', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) COMIENZAN
< p > SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length , ' &' )
SET @ BeginPos = CHARINDEX ( '& ', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
End of
- Sustituir la entidad HTML con el carácter ' '
SET @ BeginPos = CHARINDEX ('' , @ DirtyText )
SET @ endpos = @ BeginPos + 5
SET @ len = (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) comienzan
SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length ' ' )
SET @ BeginPos = CHARINDEX (' ' , @ DirtyText )
SET @ endpos = @ BeginPos + 5
SET @ Len = ( @ endpos - @ BeginPos ) + 1
End of
- Reemplace las etiquetas con una nueva línea
SET @ BeginPos = CHARINDEX ('', @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) COMENZAR
SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length , CHAR ( 13 ) + CHAR ( 10 ))
SET @ BeginPos = CHARINDEX ('', @ DirtyText )
SET @ endpos = @ BeginPos + 3
SET @ = Len (@ endpos - @ BeginPos ) + 1
End of
- Reemplace cualquier
etiquetas con una nueva línea
SET @ BeginPos = CHARINDEX ( '
', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) comienzan
< p > SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length ", CHAR ( 13 ) + CHAR ( 10 ) ')
SET @ BeginPos = CHARINDEX ( '
', @ DirtyText )
SET @ endpos = @ BeginPos + 4
SET @ = Len (@ endpos - @ BeginPos ) + 1
End of
- Reemplace las etiquetas
con una nueva línea
SET @ BeginPos = CHARINDEX ( '
', @ DirtyText )
SET @ endpos = @ BeginPos + 5
SET @ = Len (@ endpos - @ BeginPos ) + 1
WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > 0 ) comienzan
< p > SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length ", CHAR ( 13 ) + CHAR ( 10 ) ')
SET @ BeginPos = CHARINDEX ( '
', @ DirtyText )
SET @ endpos = @ BeginPos + 5
SET @ = Len (@ endpos - @ BeginPos ) + 1
End of
- Quite cualquier cosa entre las etiquetas SET @ BeginPos = CHARINDEX ( '< ', @ DirtyText ) SET @ endpos = CHARINDEX ( '>' , @ DirtyText , CHARINDEX ( '< ', @ DirtyText )) SET @ = Len (@ endpos - @ BeginPos ) + 1 WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Len > ; 0 ) COMENZAR SET @ DirtyText = COSAS (@ DirtyText , BeginPos @ , @ Length ,'') SET @ BeginPos = CHARINDEX ( '< ', @ DirtyText ) SET @ endpos = CHARINDEX ( '>' , @ DirtyText , CHARINDEX ( '< ', @ DirtyText )) SET @ = Len (@ endpos - @ BeginPos ) + 1 End of RETURN LTRIM ( RTRIM (@ DirtyText )) FIN Compile la función de SQL . ejecutar la función , y verifique que devuelve los resultados deseados. Por ejemplo : Seleccione entre dbo.CleanHTML ( ' prueba
3
4