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)

converthtml

By joy-550396,

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

 

Total article views: 730 | Views in the last 30 days: 4
 
Related Articles
FORUM

Tricky ...VARCHAR

VARCHAR logics

FORUM

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.

FORUM

sql varchar(max) to Mysql (Longtext)

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

FORUM

Insert character question

Need to insert a character into a varchar column

FORUM

Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)

Tags
conversion    
html    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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