Ako previesť HTML na text v SQL

Prevod HTML na text v SQL možno vykonať efektívne s funkciou definované užívateľom . Užívateľom definované funkcie prijíma parametre , vykoná akciu , ako je napríklad parsovanie HTML a vráti výsledok ako hodnotu . Táto funkcia môže byť vykonaná z akéhokoľvek príkazu SQL alebo externé programovací jazyk . Veci , ktoré budete potrebovať
Intermediate znalosť T - SQL
príslušné oprávnenia na vytvorenie funkcie databázový objekt
Zobraziť ďalšie inštrukcie Cestuj 1

Pripojte sa k databáze , a vytvoriť nový SQL súbor
2

Zadajte nasledujúci SQL : ..

CREATE FUNCTION [ DBO ] [ CleanHTML ]

(

@ DirtyText varchar ( mAX )

)

VRACIA varchar ( max)

BEGIN

DECLARE @ BeginPos int

DECLARE @ endpos int

DECLARE @ Ľan int

- Vymeňte subjekt HTML & s " a " charakter ( to je potrebné urobiť ako prvé , ako

- ' & ' môže byť dvojaký kódované ako " & " )

SET @ BeginPos = CHARINDEX ( ' a ' , @ DirtyText )

SET @ endpos = @ BeginPos + 4

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Ľan > 0 ) BEGIN klipart

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , ' a ' )

SET @ BeginPos = CHARINDEX ( ' a ' , @ DirtyText )

SET @ endpos = @ BeginPos + 4

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- Vymeňte HTML entity < s ' < ' znak

SET @ BeginPos = CHARINDEX ( ' < ' , @ DirtyText )

SET @ endpos = @ BeginPos + 3

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , " < ; " )

sET @ BeginPos = CHARINDEX ( ' < ' , @ DirtyText )

set @ endpos = @ BeginPos + 3

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- Vymeňte subjekt HTML > s ' > ' charakteru

SET @ BeginPos = CHARINDEX ( ' > ' , @ DirtyText )

set @ endpos = @ BeginPos + 3

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 A @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , ' > ' )

SET @ BeginPos = CHARINDEX ( ' > ' , @ DirtyText )

set @ endpos = @ BeginPos + 3

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- Vymeňte HTML entity & s ' a ' znakovej

SET @ BeginPos = CHARINDEX ( ' a ' , @ DirtyText )

SET @ endpos = @ BeginPos + 4

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 A @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , ' a ' )

SET @ BeginPos = CHARINDEX ( ' a ' , @ DirtyText )

SET @ endpos = @ BeginPos + 4

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- Vymeňte HTML entity s " " znak

sET @ BeginPos = CHARINDEX ( '' , @ DirtyText )

set @ endpos = @ BeginPos + 5

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 AND @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , " ' )

SET @ BeginPos = CHARINDEX ( ' ' , @ DirtyText )

SET @ endpos = @ BeginPos + 5

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- Vymeňte všetky tagy sa znakom nového riadku

SET @ BeginPos = CHARINDEX ( '' , @ DirtyText )

SET @ endpos = @ BeginPos + 3

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

while ( @ BeginPos > 0 AND @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , CHAR ( 13 ) + CHAR ( 10 ) )

SET @ BeginPos = CHARINDEX ( '' , @ DirtyText )

set @ endpos = @ BeginPos + 3

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- vymeňte
tagy s nového riadku

SET @ BeginPos = CHARINDEX ( '
' , @ DirtyText )

SET @ endpos = @ BeginPos + 4

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 A @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , " CHAR ( 13 ) + CHAR ( 10 ) ' )

SET @ BeginPos = CHARINDEX ( ' br ' , @ DirtyText )

set @ endpos = @ BeginPos + 4

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- vymeňte
vyhľadávacie nový riadok

SET @ BeginPos = CHARINDEX ( '
' , @ DirtyText )

SET @ endpos = @ BeginPos + 5

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

WHILE ( @ BeginPos > 0 A @ endpos > 0 AND @ Ľan > 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , " CHAR ( 13 ) + CHAR ( 10 ) ' )

SET @ BeginPos = CHARINDEX ( '
' , @ DirtyText )

set @ endpos = @ BeginPos + 5

sET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

- odstráňte všetko medzi tagy

SET @ BeginPos = CHARINDEX ( ' < ' , @ DirtyText )

SET @ endpos = CHARINDEX ( ' > ' , @ DirtyText , CHARINDEX ( ' < ' , @ DirtyText ) )

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

while ( @ BeginPos > 0 AND @ endpos > 0 AND @ Ľan > ; 0 ) BEGIN

SET @ DirtyText = STUFF ( @ DirtyText , @ BeginPos , @ Length , '' )

SET @ BeginPos = CHARINDEX ( ' < ' , @ DirtyText )

SET @ endpos = CHARINDEX ( ' > ' , @ DirtyText , CHARINDEX ( ' < ' , @ DirtyText ) )

SET @ Ľan = ( @ endpos - @ BeginPos ) + 1

END

RETURN LTrim ( RTrim ( @ DirtyText ) )

END
3

Kompilácia funkciu SQL .

4

spustite funkciu , a overte , že vracia požadované výsledky . Napríklad :

Vyberte si z dbo.CleanHTML ( " testovacie < /BODY > < /HTML > ' ) ;

• Predchádzajúca strana: Jednoduché Zdrojový kód pre budovanie webovej stránky 
• Ďalšia strana: Ako používať SQL premenné v Oracle