SQL Server – Função que limpa caracteres em HTML, CSS e ASCII do campo que desejar, no SQL. Contribuição/Créditos ao Andros Albert Cavalin
CREATE FUNCTION [dbo].[fncRemove_HTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
-- Substitui a entidade HTML "&" pelo caracter ''
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Substitui a entidade HTML "<" pelo caracter '<'
SET @Start = CHARINDEX('<', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX('<', @HTMLText) SET @End = @Start + 3 SET @Length = (@End - @Start) + 1 END -- Substitui a entidade HTML ">" pelo caracter '>'
SET @Start = CHARINDEX('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Substitui a entidade HTML "&&" pelo caracter ''
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Substitui a entidade HTML " " pelo caracter ' '
SET @Start = CHARINDEX(' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX(' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END
-- Substitui a tag por ''
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END
-- Substitui a tag por '')
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END
-- Substitui a tag
por ''
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('
', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END
-- Remove os parâmetros contidos nas tags HTML
SET @Start = CHARINDEX('<', @HTMLText) SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText)) SET @Length = (@End - @Start) + 1 WHILE (@Start > 0 AND @End > 0 AND @Length > 0)
BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('<', @HTMLText) SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END
SET @HTMLText = REPLACE(@HTMLText,'º','') -- º = ""
SET @HTMLText = REPLACE(@HTMLText,''','') -- ' = ""
SET @HTMLText = REPLACE(@HTMLText,'”','') -- ” = ""
SET @HTMLText = REPLACE(@HTMLText,'"','') -- " = ""
SET @HTMLText = REPLACE(@HTMLText,'–','') -- – = ""
SET @HTMLText = REPLACE(@HTMLText,'§','') -- § = ""
SET @HTMLText = REPLACE(@HTMLText,'·','') -- · = ""
SET @HTMLText = REPLACE(@HTMLText,'“','') -- “ = ""
SET @HTMLText = REPLACE(@HTMLText,'á','á') -- á = "á"
SET @HTMLText = REPLACE(@HTMLText,'ã','ã') -- ã = "ã"
SET @HTMLText = REPLACE(@HTMLText,'â','â') -- â = "â"
SET @HTMLText = REPLACE(@HTMLText,'à','à') -- à = "à"
SET @HTMLText = REPLACE(@HTMLText,'é','é') -- é = "é"
SET @HTMLText = REPLACE(@HTMLText,'ê','ê') -- ê = "ê"
SET @HTMLText = REPLACE(@HTMLText,'í','í') -- í = "í"
SET @HTMLText = REPLACE(@HTMLText,'ó','ó') -- ó = "ó"
SET @HTMLText = REPLACE(@HTMLText,'õ','õ') -- õ = "õ"
SET @HTMLText = REPLACE(@HTMLText,'ô','ô') -- ô = "ô"
SET @HTMLText = REPLACE(@HTMLText,'ú','ú') -- ú = "ú"
SET @HTMLText = REPLACE(@HTMLText,'ç','ç') -- ç = "ç"
RETURN LTRIM(RTRIM(@HTMLText))
END
Forma de uso:
SELECT [dbo].[fncRemove_HTML](campo_com_tags_html) as campo_sem_html FROM sua_tabela