CREATE FUNCTION HTMLEncode(@OriginalText VARCHAR(4000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000);
WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
-- E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
-- E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E04)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =34) THEN '"' --" " " quotation mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =39) THEN ''' --' ' ' apostrophe
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =38) THEN '&' --& & & ampersand
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =60) THEN '<' --< < < less-than
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =62) THEN '>' --> > > greater-than
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =160) THEN ' ' -- non-breaking space
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =161) THEN '¡' --¡ ¡ ¡ inverted exclamation mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =162) THEN '¢' --¢ ¢ ¢ cent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =163) THEN '£' --£ £ £ pound
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =164) THEN '¤' --¤ ¤ ¤ currency
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =165) THEN '¥' --¥ ¥ ¥ yen
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =166) THEN '¦' --¦ ¦ ¦ broken vertical bar
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =167) THEN '§' --§ § § section
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =168) THEN '¨' --¨ ¨ ¨ spacing diaeresis
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =169) THEN '©' --© © © copyright
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =170) THEN 'ª' --ª ª ª feminine ordinal indicator
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =171) THEN '«' --« « « angle quotation mark (left)
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =172) THEN '¬' --¬ ¬ ¬ negation
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =173) THEN '' -- soft hyphen
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =174) THEN '®' --® ® ® registered trademark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =175) THEN '¯' --¯ ¯ ¯ spacing macron
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =176) THEN '°' --° ° ° degree
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =177) THEN '±' --± ± ± plus-or-minus
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =178) THEN '²' --² ² ² superscript 2
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =179) THEN '³' --³ ³ ³ superscript 3
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =180) THEN '´' --´ ´ ´ spacing acute
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =181) THEN 'µ' --µ µ µ micro
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =182) THEN '¶' --¶ ¶ ¶ paragraph
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =183) THEN '·' --· · · middle dot
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =184) THEN '¸' --¸ ¸ ¸ spacing cedilla
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =185) THEN '¹' --¹ ¹ ¹ superscript 1
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =186) THEN 'º' --º º º masculine ordinal indicator
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =187) THEN '»' --» » » angle quotation mark (right)
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =188) THEN '¼' --¼ ¼ ¼ fraction 1/4
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =189) THEN '½' --½ ½ ½ fraction 1/2
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =190) THEN '¾' --¾ ¾ ¾ fraction 3/4
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =191) THEN '¿' --¿ ¿ ¿ inverted question mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =215) THEN '×' --× × × multiplication
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =247) THEN '÷' --÷ ÷ ÷ division
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =192) THEN 'À' --À À À capital a, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =193) THEN 'Á' --Á Á Á capital a, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =194) THEN 'Â' --Â Â Â capital a, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =195) THEN 'Ã' --Ã Ã Ã capital a, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =196) THEN 'Ä' --Ä Ä Ä capital a, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =197) THEN 'Å' --Å Å Å capital a, ring
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =198) THEN 'Æ' --Æ Æ Æ capital ae
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =199) THEN 'Ç' --Ç Ç Ç capital c, cedilla
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =200) THEN 'È' --È È È capital e, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =201) THEN 'É' --É É É capital e, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =202) THEN 'Ê' --Ê Ê Ê capital e, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =203) THEN 'Ë' --Ë Ë Ë capital e, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =204) THEN 'Ì' --Ì Ì Ì capital i, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =205) THEN 'Í' --Í Í Í capital i, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =206) THEN 'Î' --Î Î Î capital i, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =207) THEN 'Ï' --Ï Ï Ï capital i, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =208) THEN 'Ð' --Ð Ð Ð capital eth, Icelandic
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =209) THEN 'Ñ' --Ñ Ñ Ñ capital n, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =210) THEN 'Ò' --Ò Ò Ò capital o, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =211) THEN 'Ó' --Ó Ó Ó capital o, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =212) THEN 'Ô' --Ô Ô Ô capital o, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =213) THEN 'Õ' --Õ Õ Õ capital o, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =214) THEN 'Ö' --Ö Ö Ö capital o, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =216) THEN 'Ø' --Ø Ø Ø capital o, slash
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =217) THEN 'Ù' --Ù Ù Ù capital u, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =218) THEN 'Ú' --Ú Ú Ú capital u, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =219) THEN 'Û' --Û Û Û capital u, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =220) THEN 'Ü' --Ü Ü Ü capital u, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =221) THEN 'Ý' --Ý Ý Ý capital y, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =222) THEN 'Þ' --Þ Þ Þ capital THORN, Icelandic
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =223) THEN 'ß' --ß ß ß small sharp s, German
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =224) THEN 'à' --à à à small a, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =225) THEN 'á' --á á á small a, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =226) THEN 'â' --â â â small a, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =227) THEN 'ã' --ã ã ã small a, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =228) THEN 'ä' --ä ä ä small a, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =229) THEN 'å' --å å å small a, ring
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =230) THEN 'æ' --æ æ æ small ae
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =231) THEN 'ç' --ç ç ç small c, cedilla
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =232) THEN 'è' --è è è small e, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =233) THEN 'é' --é é é small e, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =234) THEN 'ê' --ê ê ê small e, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =235) THEN 'ë' --ë ë ë small e, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =236) THEN 'ì' --ì ì ì small i, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =237) THEN 'í' --í í í small i, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =238) THEN 'î' --î î î small i, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =239) THEN 'ï' --ï ï ï small i, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =240) THEN 'ð' --ð ð ð small eth, Icelandic
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =241) THEN 'ñ' --ñ ñ ñ small n, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =242) THEN 'ò' --ò ò ò small o, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =243) THEN 'ó' --ó ó ó small o, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =244) THEN 'ô' --ô ô ô small o, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =245) THEN 'õ' --õ õ õ small o, tilde
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =246) THEN 'ö' --ö ö ö small o, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =248) THEN 'ø' --ø ø ø small o, slash
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =249) THEN 'ù' --ù ù ù small u, grave accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =250) THEN 'ú' --ú ú ú small u, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =251) THEN 'û' --û û û small u, circumflex accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =252) THEN 'ü' --ü ü ü small u, umlaut mark
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =253) THEN 'ý' --ý ý ý small y, acute accent
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =254) THEN 'þ' --þ þ þ small thorn, Icelandic
WHEN(ASCII(SUBSTRING(@OriginalText,Tally.N,1)) =255) THEN 'ÿ' --ÿ ÿ ÿ small y, umlaut mark
ELSE SUBSTRING(@OriginalText,Tally.N,1)
END
FROM Tally
WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END