SQL User Defined Function to strip HTML tags and replace HTML Entities

Question

I'm trying to write a UDF (actually I'm adapting some code I've found around the web into a single function) to do what is described in the title.

Here's the code:

declare @txt varchar(max), @start int, @end int, @len int

set @txt = '<p class=&#34;answer&#34;>Informamos que a documenta&ccedil;&atilde;o <strong>deve ser impressa e enviada fisicamente pela AG&Ecirc;NCIA</strong>, contendo confere com oringinal por funcion&aacute;rio CAIXA.</p>'
set @start = charindex('<',@txt)
set @end = charindex('>',@txt,@start)
set @len = (@end - @start) + 1

while @start > 0 and @end > 0 and @len > 0
begin
    set @txt = stuff(@txt,@start,@len,'')
    set @start = charindex('<',@txt)
    set @end = charindex('>',@txt,@start)
    set @len = (@end - @start) + 1
end

SET @txt = REPLACE(@txt,'&nbsp;',' ') --space
SET @txt = REPLACE(@txt,'&ldquo;',CHAR(34)) --"
SET @txt = REPLACE(@txt,'&rdquo;',CHAR(34)) --"
SET @txt = REPLACE(@txt,'&lsquo;',CHAR(39)) --'
SET @txt = REPLACE(@txt,'&rsquo;',CHAR(39)) --'
SET @txt = REPLACE(@txt,'&ndash;',CHAR(150)) -- –
SET @txt = REPLACE(@txt,'&mdash;',CHAR(151)) -- —
SET @txt = REPLACE(@txt,'&ordm;',CHAR(186)) -- º
SET @txt = REPLACE(@txt,'&ordf;',CHAR(170)) -- ª
SET @txt = REPLACE(@txt,'&sect;',CHAR(167)) -- §
--------------------------------------------------------------
SET @txt = REPLACE(@txt,'&#34;',CHAR(34)) --"
SET @txt = REPLACE(@txt,'&#39;',CHAR(39)) --'
--------------------------------------------------------------

SET @txt = REPLACE(@txt,'&agrave;','à') --à
SET @txt = REPLACE(@txt,'&aacute;','á') --á
SET @txt = REPLACE(@txt,'&atilde;','ã') --ã
SET @txt = REPLACE(@txt,'&acirc;','â') --â
SET @txt = REPLACE(@txt,'&auml;','ä') --ä
SET @txt = REPLACE(@txt,'&eacute;','é') --é
SET @txt = REPLACE(@txt,'&ecirc;','ê') --ê
SET @txt = REPLACE(@txt,'&iacute;','í') --í
SET @txt = REPLACE(@txt,'&oacute;','ó') --ó
SET @txt = REPLACE(@txt,'&otilde;','õ') --õ     
SET @txt = REPLACE(@txt,'&oslash;','ø') --ø
SET @txt = REPLACE(@txt,'&uacute;','ú') --ú
SET @txt = REPLACE(@txt,'&uuml;','ü') --ü
SET @txt = REPLACE(@txt,'&ccedil;','ç') --ç
--------------------------------------------------------------
SET @txt = REPLACE(@txt,'&Agrave;',CHAR(192)) --À
SET @txt = REPLACE(@txt,'&Aacute;',CHAR(193)) --Á
SET @txt = REPLACE(@txt,'&Atilde;',CHAR(195)) --Ã
SET @txt = REPLACE(@txt,'&Acirc;',CHAR(194)) --Â
SET @txt = REPLACE(@txt,'&Auml;',CHAR(196)) --Ä
SET @txt = REPLACE(@txt,'&Eacute;',CHAR(201)) --É
SET @txt = REPLACE(@txt,'&Ecirc;',CHAR(202)) --Ê
SET @txt = REPLACE(@txt,'&Iacute;',CHAR(205)) --Í
SET @txt = REPLACE(@txt,'&Oacute;',CHAR(211)) --Ó
SET @txt = REPLACE(@txt,'&Otilde;',CHAR(213)) --Õ
SET @txt = REPLACE(@txt,'&Oslash;',CHAR(216)) --Ø
SET @txt = REPLACE(@txt,'&Uacute;',CHAR(218)) --Ú
SET @txt = REPLACE(@txt,'&Uuml;',CHAR(220)) --Ü
SET @txt = REPLACE(@txt,'&Ccedil;',CHAR(199)) --Ç

select LTRIM(RTRIM(@txt))

It's stripping the HTML tags an converting ONLY the lowercase HTML entities, not working when finding uppercase like Ê in the word AGÊNCIA (AGÊNCIA), printing AGêNCIA instead.

Any help to make it work properly?

Edit: PS: I can't change my DB Collation, as suggested by @dzomba

try this stackoverflow.com/questions/457701/…

Sushil
– Sushil

2015-07-11 00:06:39 +00:00
Commented Jul 11, 2015 at 0:06 — Sushil
– Sushil, Commented Jul 11, 2015 at 0:06

danvasiloiu · Accepted Answer · 2015-07-13 20:30:07Z

Well if you want that you will need to set you db collation as case sensitive.

UPDATE! This is a workaround but i think that will do the job. i don't have a sql server to test it but i'm almost sure that it works just fine.

declare @txt varchar(max)
declare @start int
declare @end int
declare @len int

set @txt = '<p class=&#34;answer&#34;>Informamos que a documenta&ccedil;&atilde;o <strong>deve ser impressa e enviada fisicamente pela AG&Ecirc;NCIA</strong>, contendo confere com oringinal por funcion&aacute;rio CAIXA.</p>'
set @start = charindex('<',@txt)
set @end = charindex('>',@txt,@start)
set @len = (@end - @start) + 1

while @start > 0 and @end > 0 and @len > 0
begin
    set @txt = stuff(@txt,@start,@len,'')
    set @start = charindex('<',@txt)
    set @end = charindex('>',@txt,@start)
    set @len = (@end - @start) + 1
end

DECLARE @table (txtColumn varchar(max) COLLATE SQL_Latin1_General_CP1_CS_AS ) --make the column case sensitive


INSERT INTO @table (txtColumn)
SELECT @txt

UPDATE @table set txtColumn = REPLACE(txtColumn,'&nbsp;',' ') --space
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ldquo;',CHAR(34)) --"
UPDATE @table set txtColumn = REPLACE(txtColumn,'&rdquo;',CHAR(34)) --"
UPDATE @table set txtColumn = REPLACE(txtColumn,'&lsquo;',CHAR(39)) --'
UPDATE @table set txtColumn = REPLACE(txtColumn,'&rsquo;',CHAR(39)) --'
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ndash;',CHAR(150)) -- –
UPDATE @table set txtColumn = REPLACE(txtColumn,'&mdash;',CHAR(151)) -- —
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ordm;',CHAR(186)) -- º
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ordf;',CHAR(170)) -- ª
UPDATE @table set txtColumn = REPLACE(txtColumn,'&sect;',CHAR(167)) -- §
    -------------------------------------------------------------
UPDATE @table set txtColumn = REPLACE(txtColumn,'&#34;',CHAR(34)) --"
UPDATE @table set txtColumn = REPLACE(txtColumn,'&#39;',CHAR(39)) --'
    --------------------------------------------------------------

UPDATE @table set txtColumn = REPLACE(txtColumn,'&agrave;','à') --à
UPDATE @table set txtColumn = REPLACE(txtColumn,'&aacute;','á') --á
UPDATE @table set txtColumn = REPLACE(txtColumn,'&atilde;','ã') --ã
UPDATE @table set txtColumn = REPLACE(txtColumn,'&acirc;','â') --â
UPDATE @table set txtColumn = REPLACE(txtColumn,'&auml;','ä') --ä
UPDATE @table set txtColumn = REPLACE(txtColumn,'&eacute;','é') --é
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ecirc;','ê') --ê
UPDATE @table set txtColumn = REPLACE(txtColumn,'&iacute;','í') --í
UPDATE @table set txtColumn = REPLACE(txtColumn,'&oacute;','ó') --ó
UPDATE @table set txtColumn = REPLACE(txtColumn,'&otilde;','õ') --õ     
UPDATE @table set txtColumn = REPLACE(txtColumn,'&oslash;','ø') --ø
UPDATE @table set txtColumn = REPLACE(txtColumn,'&uacute;','ú') --ú
UPDATE @table set txtColumn = REPLACE(txtColumn,'&uuml;','ü') --ü
UPDATE @table set txtColumn = REPLACE(txtColumn,'&ccedil;','ç') --ç
    -------------------------------------------------------------
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Agrave;',CHAR(192)) --À
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Aacute;',CHAR(193)) --Á
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Atilde;',CHAR(195)) --Ã
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Acirc;',CHAR(194)) --Â
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Auml;',CHAR(196)) --Ä
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Eacute;',CHAR(201)) --É
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Ecirc;',CHAR(202)) --Ê
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Iacute;',CHAR(205)) --Í
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Oacute;',CHAR(211)) --Ó
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Otilde;',CHAR(213)) --Õ
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Oslash;',CHAR(216)) --Ø
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Uacute;',CHAR(218)) --Ú
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Uuml;',CHAR(220)) --Ü
UPDATE @table set txtColumn = REPLACE(txtColumn,'&Ccedil;',CHAR(199)) --Ç

    SELECT LTRIM(RTRIM(txtColumn)) FROM @table

--- IN THE END DROP THE TABLE
    DROP TABLE @table

The problem is that I can't do that.. Thanks anyway, I'm goint to edit my question with your observation...

Collectives™ on Stack Overflow

SQL User Defined Function to strip HTML tags and replace HTML Entities

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related