Description
The function wull convert html reserved words to their corresponding character like '<' to '<' or 'ä' to 'ä'. The ' ' 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, kö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), ' ')
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
