Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)


By joy-550396,


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


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


Hallo, könnt Ihr die User bitte anlegen



if object_id ('dbo.iv_converthtml') is not null
drop function dbo.iv_converthtml
create function dbo.iv_converthtml
(@htmltext varchar(max))
returns varchar(max)
@charvalue varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@charcode varchar(25) --collate SQL_Latin1_General_CP1_CS_AS
,@text varchar(max)
@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
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

return @text
grant execute on dbo.iv_converthtml to public


Total article views: 743 | Views in the last 30 days: 2
Related Articles

Tricky ...VARCHAR

VARCHAR logics


I would like to insert from the clipboard text with line breaks in a "varchar" column.

I would like to insert from the clipboard text with line breaks in a "varchar" column.


sql varchar(max) to Mysql (Longtext)

can't insert data from sql varchar(max) into Mysql(longtext)


Insert character question

Need to insert a character into a varchar column


Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones