Cómo convertir HTML a texto en SQL

Convertir HTML a texto en SQL se puede hacer de manera eficiente con una función definida por el usuario . Una función definida por el usuario acepta parámetros , lleva a cabo una acción, tales como análisis de HTML , y devuelve el resultado como un valor. La función se puede ejecutar desde cualquier sentencia SQL o del lenguaje de programación externo . Cosas que necesitará
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
3

Compile la función de SQL .

4

ejecutar la función , y verifique que devuelve los resultados deseados. Por ejemplo :

Seleccione entre dbo.CleanHTML ( ' prueba < /BODY> < /HTML >');