converthtml

,

Description

The function wull convert html reserved words to their corresponding character like '&lt;' to '<' or '&auml;' to 'ä'. The '&nbsp;' is mapped to a carriadge return in stead of a nonbreaking space, as it turned out more convenient in my case.

 

How to use

query:

select dbo.iv_converthtml ('Hallo,&nbsp;k&ouml;nnt Ihr die User bitte anlegen')

result:

Hallo, könnt Ihr die User bitte anlegen

 

Script

if object_id ('dbo.iv_converthtml') is not null
drop function dbo.iv_converthtml
go
create function dbo.iv_converthtml
(@htmltext varchar(max))
returns varchar(max)
as
begin
declare
@charvalue varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@charcode varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@text varchar(max)
declare
@htmltable table
(charvalue varchar(25) collate SQL_Latin1_General_CP1_CS_AS --case sensitive
, charcode varchar(25) collate SQL_Latin1_General_CP1_CS_AS
)

insert @htmltable values(char(10), '&nbsp;')
insert @htmltable values('"', '&quot;')
insert @htmltable values('''','&apos;')
insert @htmltable values('&', '&amp;')
insert @htmltable values('<', '&lt;')
insert @htmltable values('>', '&gt;')
insert @htmltable values('À', '&Agrave;')
insert @htmltable values('Á', '&Aacute;')
insert @htmltable values('Â', '&Acirc;')
insert @htmltable values('Ã', '&Atilde;')
insert @htmltable values('Ä', '&Auml;')
insert @htmltable values('Å', '&Aring;')
insert @htmltable values('Æ', '&AElig;')
insert @htmltable values('Ç', '&Ccedil;')
insert @htmltable values('È', '&Egrave;')
insert @htmltable values('É', '&Eacute;')
insert @htmltable values('Ê', '&Ecirc;')
insert @htmltable values('Ë', '&Euml;')
insert @htmltable values('Ì', '&Igrave;')
insert @htmltable values('Í', '&Iacute;')
insert @htmltable values('Î', '&Icirc;')
insert @htmltable values('Ï', '&Iuml;')
insert @htmltable values('Ð', '&ETH;')
insert @htmltable values('Ñ', '&Ntilde;')
insert @htmltable values('Ò', '&Ograve;')
insert @htmltable values('Ó', '&Oacute;')
insert @htmltable values('Ô', '&Ocirc;')
insert @htmltable values('Õ', '&Otilde;')
insert @htmltable values('Ö', '&Ouml;')
insert @htmltable values('Ø', '&Oslash;')
insert @htmltable values('Ù', '&Ugrave;')
insert @htmltable values('Ú', '&Uacute;')
insert @htmltable values('Û', '&Ucirc;')
insert @htmltable values('Ü', '&Uuml;')
insert @htmltable values('Ý', '&Yacute;')
insert @htmltable values('Þ', '&THORN;')
insert @htmltable values('ß', '&szlig;')
insert @htmltable values('à', '&agrave;')
insert @htmltable values('á', '&aacute;')
insert @htmltable values('â', '&acirc;')
insert @htmltable values('ã', '&atilde;')
insert @htmltable values('ä', '&auml;')
insert @htmltable values('å', '&aring;')
insert @htmltable values('æ', '&aelig;')
insert @htmltable values('ç', '&ccedil;')
insert @htmltable values('è', '&egrave;')
insert @htmltable values('é', '&eacute;')
insert @htmltable values('ê', '&ecirc;')
insert @htmltable values('ë', '&euml;')
insert @htmltable values('ì', '&igrave;')
insert @htmltable values('í', '&iacute;')
insert @htmltable values('î', '&icirc;')
insert @htmltable values('ï', '&iuml;')
insert @htmltable values('ð', '&eth;')
insert @htmltable values('ñ', '&ntilde;')
insert @htmltable values('ò', '&ograve;')
insert @htmltable values('ó', '&oacute;')
insert @htmltable values('ô', '&ocirc;')
insert @htmltable values('õ', '&otilde;')
insert @htmltable values('ö', '&ouml;')
insert @htmltable values('ø', '&oslash;')
insert @htmltable values('ù', '&ugrave;')
insert @htmltable values('ú', '&uacute;')
insert @htmltable values('û', '&ucirc;')
insert @htmltable values('ü', '&uuml;')
insert @htmltable values('ý', '&yacute;')
insert @htmltable values('þ', '&thorn;')
insert @htmltable values('ÿ', '&yuml;')

set @text = @htmltext
while (select count(*) from @htmltable) > 0
begin
select top 1 @charvalue = charvalue, @charcode = charcode
from @htmltable

set @text = replace (@text collate SQL_Latin1_General_CP1_CS_AS, @charcode, @charvalue)

delete @htmltable
where charcode = @charcode

end
return @text
end
go
grant execute on dbo.iv_converthtml to public

 

if object_id ('dbo.iv_converthtml') is not null
    drop function dbo.iv_converthtml
go
create function dbo.iv_converthtml
 (@htmltext varchar(max))
    returns varchar(max)
as
begin
declare
@charvalue varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@charcode varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@text varchar(max)
declare
@htmltable table 
    (charvalue varchar(25) collate SQL_Latin1_General_CP1_CS_AS --case sensitive
    , charcode varchar(25) collate SQL_Latin1_General_CP1_CS_AS
    )

insert @htmltable values(char(10), '')
insert @htmltable values('"', '"')
insert @htmltable values('''',''')
insert @htmltable values('&', '&')
insert @htmltable values('<', '<')
insert @htmltable values('>', '>')
insert @htmltable values('À', 'À')
insert @htmltable values('Á', 'Á')
insert @htmltable values('Â', 'Â')
insert @htmltable values('Ã', 'Ã')
insert @htmltable values('Ä', 'Ä')
insert @htmltable values('Å', 'Å')
insert @htmltable values('Æ', 'Æ')
insert @htmltable values('Ç', 'Ç')
insert @htmltable values('È', 'È')
insert @htmltable values('É', 'É')
insert @htmltable values('Ê', 'Ê') 
insert @htmltable values('Ë', 'Ë') 
insert @htmltable values('Ì', 'Ì')
insert @htmltable values('Í', 'Í') 
insert @htmltable values('Î', 'Î') 
insert @htmltable values('Ï', 'Ï') 
insert @htmltable values('Ð', 'Ð') 
insert @htmltable values('Ñ', 'Ñ') 
insert @htmltable values('Ò', 'Ò')
insert @htmltable values('Ó', 'Ó')
insert @htmltable values('Ô', 'Ô') 
insert @htmltable values('Õ', 'Õ')
insert @htmltable values('Ö', 'Ö') 
insert @htmltable values('Ø', 'Ø')
insert @htmltable values('Ù', 'Ù')
insert @htmltable values('Ú', 'Ú')
insert @htmltable values('Û', 'Û')
insert @htmltable values('Ü', 'Ü') 
insert @htmltable values('Ý', 'Ý')
insert @htmltable values('Þ', 'Þ') 
insert @htmltable values('ß', 'ß') 
insert @htmltable values('à', 'à')
insert @htmltable values('á', 'á')
insert @htmltable values('â', 'â') 
insert @htmltable values('ã', 'ã')
insert @htmltable values('ä', 'ä') 
insert @htmltable values('å', 'å')
insert @htmltable values('æ', 'æ')
insert @htmltable values('ç', 'ç')
insert @htmltable values('è', 'è')
insert @htmltable values('é', 'é')
insert @htmltable values('ê', 'ê') 
insert @htmltable values('ë', 'ë') 
insert @htmltable values('ì', 'ì')
insert @htmltable values('í', 'í')
insert @htmltable values('î', 'î') 
insert @htmltable values('ï', 'ï') 
insert @htmltable values('ð', 'ð') 
insert @htmltable values('ñ', 'ñ')
insert @htmltable values('ò', 'ò')
insert @htmltable values('ó', 'ó')
insert @htmltable values('ô', 'ô') 
insert @htmltable values('õ', 'õ')
insert @htmltable values('ö', 'ö') 
insert @htmltable values('ø', 'ø')
insert @htmltable values('ù', 'ù')
insert @htmltable values('ú', 'ú')
insert @htmltable values('û', 'û') 
insert @htmltable values('ü', 'ü') 
insert @htmltable values('ý', 'ý')
insert @htmltable values('þ', 'þ') 
insert @htmltable values('ÿ', 'ÿ') 

set @text = @htmltext
while (select count(*) from @htmltable) > 0
begin 
    select top 1 @charvalue = charvalue, @charcode = charcode
    from @htmltable

    set @text = replace (@text collate SQL_Latin1_General_CP1_CS_AS, @charcode, @charvalue)
    
    delete @htmltable 
    where charcode = @charcode

end
return @text
end
go
grant execute on dbo.iv_converthtml to public

Rate

4 (1)

Share

Share

Rate

4 (1)