Is there any function to encode HTML strings in T-SQL? I have a legacy database which contains dodgey characters such as '<', '>' etc. I can write a function to replace the characters but is there a better way?
I have an ASP.Net application and when it returns a string it contains characters which cause an error. The ASP.Net application is reading the data from a database table. It does not write to the table itself.
We have a legacy system that uses a trigger and dbmail to send HTML encoded email when a table is entered, so we require encoding within the email generation. I noticed that Leo's version has a slight bug that encodes the & in <
and >
I use this version:
CREATE FUNCTION HtmlEncode
(
@UnEncoded as varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Encoded as varchar(500)
--order is important here. Replace the amp first, then the lt and gt.
--otherwise the < will become &lt;
SELECT @Encoded =
Replace(
Replace(
Replace(@UnEncoded,'&','&'),
'<', '<'),
'>', '>')
RETURN @Encoded
END
GO