T-SQL function to HTML Decode

Sometimes you need to decode some text on the database side.
Sometimes you don’t.
In case you need to, here is one special function.

There will be some additions to it, to a different encode type soon.


GO
IF OBJECT_ID('dbo.fn_HTMLDecode') IS NOT NULL BEGIN DROP FUNCTION dbo.fn_HTMLDecode END
GO
CREATE FUNCTION dbo.fn_HTMLDecode(
    @vcWhat NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @vcResult NVARCHAR(MAX);
    DECLARE @siPos INT ,@vcEncoded NVARCHAR(9) ,@siChar INT;
    SET @vcResult = RTRIM(LTRIM(CAST(REPLACE(@vcWhat COLLATE Latin1_General_BIN, CHAR(0), '') AS NVARCHAR(MAX))));
    SELECT @vcResult = REPLACE(REPLACE(@vcResult, ' ', ' '), ' ', ' ');
    IF @vcResult = '' RETURN @vcResult;

    declare @s varchar(35);
    declare @n int; set @n = 6;
    declare @i int;

    while @n > 2
    begin
        set @s = '';
        set @i=1;
        while @i<=@n
        begin
            set @s = @s + '[0-9]';
            set @i = @i + 1;
        end
        set @s = '%&#' + @s + '%';
        SELECT @siPos = PATINDEX(@s, @vcResult);
        WHILE @siPos > 0
        BEGIN
            SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, @n+3)
                ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, @n) AS INT)
                ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
                ,@siPos = PATINDEX(@s, @vcResult);
        END
        set @n = @n - 1;
    end

	select @vcResult = REPLACE(REPLACE(@vcResult, NCHAR(160), ' '), CHAR(160), ' ');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult, '&', '&'), '"', '"'), '<', '<'), '>', '>'), '&amp;', '&'),'”','”'),'„','„'),'–','–'),'—','—');

	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Š','Š') COLLATE Latin1_General_BIN,'š','š') COLLATE Latin1_General_BIN,'Ç','Ç') COLLATE Latin1_General_BIN,'ç','ç');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult,'À','À') COLLATE Latin1_General_BIN,'à','à') COLLATE Latin1_General_BIN,'Á','Á') COLLATE Latin1_General_BIN,'á','á') COLLATE Latin1_General_BIN,'Â','Â') COLLATE Latin1_General_BIN,'â','â') COLLATE Latin1_General_BIN,'Ã','Ã') COLLATE Latin1_General_BIN,'ã','ã') COLLATE Latin1_General_BIN,'Ä','Ä') COLLATE Latin1_General_BIN,'ä','ä') COLLATE Latin1_General_BIN,'Å','Å') COLLATE Latin1_General_BIN,'å','å') COLLATE Latin1_General_BIN,'Æ','Æ') COLLATE Latin1_General_BIN,'æ','æ');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'È','È') COLLATE Latin1_General_BIN,'è','è') COLLATE Latin1_General_BIN,'É','É') COLLATE Latin1_General_BIN,'é','é') COLLATE Latin1_General_BIN,'Ê','Ê') COLLATE Latin1_General_BIN,'ê','ê') COLLATE Latin1_General_BIN,'Ë','Ë') COLLATE Latin1_General_BIN,'ë','ë');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ì','Ì') COLLATE Latin1_General_BIN,'ì','ì') COLLATE Latin1_General_BIN,'Í','Í') COLLATE Latin1_General_BIN,'í','í') COLLATE Latin1_General_BIN,'Î','Î') COLLATE Latin1_General_BIN,'î','î') COLLATE Latin1_General_BIN,'Ï','Ï') COLLATE Latin1_General_BIN,'ï','ï');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ò','Ò') COLLATE Latin1_General_BIN,'ò','ò') COLLATE Latin1_General_BIN,'Ó','Ó') COLLATE Latin1_General_BIN,'ó','ó') COLLATE Latin1_General_BIN,'Ô','Ô') COLLATE Latin1_General_BIN,'ô','ô') COLLATE Latin1_General_BIN,'Õ','Õ') COLLATE Latin1_General_BIN,'õ','õ') COLLATE Latin1_General_BIN,'Ö','Ö') COLLATE Latin1_General_BIN,'ö','ö') COLLATE Latin1_General_BIN,'Ø','Ø') COLLATE Latin1_General_BIN,'ø','ø');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ù','Ù') COLLATE Latin1_General_BIN,'ù','ù') COLLATE Latin1_General_BIN,'Ú','Ú') COLLATE Latin1_General_BIN,'ú','ú') COLLATE Latin1_General_BIN,'Û','Û') COLLATE Latin1_General_BIN,'û','û') COLLATE Latin1_General_BIN,'Ü','Ü') COLLATE Latin1_General_BIN,'ü','ü');
	select @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult COLLATE Latin1_General_BIN,'Ð','Ð') COLLATE Latin1_General_BIN,'ð','ð') COLLATE Latin1_General_BIN,'Ñ','Ñ') COLLATE Latin1_General_BIN,'ñ','ñ') COLLATE Latin1_General_BIN,'Ý','Ý') COLLATE Latin1_General_BIN,'ý','ý') COLLATE Latin1_General_BIN,'Þ','Þ') COLLATE Latin1_General_BIN,'þ','þ') COLLATE Latin1_General_BIN,'ß','ß');

    RETURN @vcResult;
END

1 thoughts on “T-SQL function to HTML Decode”

  1. I am writing to let you know of the amazing encounter my daughter developed viewing your site. She picked up plenty of pieces, which included what it is like to have an excellent teaching mindset to get folks just thoroughly grasp certain tricky matters. You really did more than our own expected results. I appreciate you for presenting such productive, safe, informative and in addition cool guidance on the topic to Sandra.

  2. I simply wanted to appreciate you once more. I do not know the things I could possibly have sorted out without the actual recommendations shared by you on this situation. Completely was a real scary setting for me, however , encountering the very skilled approach you handled that took me to cry with delight. I’m happy for your information and even believe you know what a great job you happen to be providing educating other individuals through your webblog. Probably you have never encountered any of us.

  3. Thanks for each of your labor on this blog. Kim really loves engaging in research and it is obvious why. All of us hear all of the powerful form you present precious tricks via this website and in addition strongly encourage response from website visitors about this area then my child is certainly learning so much. Enjoy the remaining portion of the new year. You are always conducting a wonderful job.

  4. I simply wanted to say thanks again. I do not know the things that I would’ve undertaken in the absence of the actual secrets shared by you relating to such a question. It has been a daunting circumstance for me personally, nevertheless understanding a new skilled tactic you processed the issue forced me to weep with joy. I am just grateful for this information and pray you find out what a powerful job you happen to be providing training people today by way of a web site. I know that you have never come across all of us.

  5. I precisely had to thank you so much yet again. I am not sure what I could possibly have used without these solutions shown by you on such area. It had become a very hard scenario in my opinion, however , taking note of your expert form you processed that forced me to cry for contentment. Now i’m happy for the assistance and in addition hope you comprehend what a powerful job you’re accomplishing instructing others through the use of your site. I am sure you’ve never come across any of us.

  6. I would like to show some thanks to this writer for bailing me out of such a scenario. Right after surfing throughout the the web and obtaining advice which are not productive, I was thinking my life was done. Being alive without the answers to the problems you have fixed as a result of your main blog post is a critical case, as well as the ones which might have negatively affected my entire career if I had not noticed your web page. Your main natural talent and kindness in touching the whole lot was important. I am not sure what I would’ve done if I had not come upon such a point like this. I’m able to at this moment relish my future. Thanks a lot very much for your expert and results-oriented guide. I will not be reluctant to refer your web blog to anybody who desires support about this subject.

  7. A lot of thanks for every one of your labor on this website. My mom takes pleasure in going through investigation and it’s obvious why. Almost all notice all about the powerful ways you give informative tips on your blog and therefore strongly encourage contribution from visitors on this concern plus our own girl has been becoming educated a lot of things. Take pleasure in the remaining portion of the year. You are conducting a tremendous job.

  8. (BBC) – Relaxed Bleeding and Bruising. Your liver makes the things that hands your blood clot. When it’s damaged, it can’t make good enough. You superiority start to bleed surely and secure bother stopping it. Or you muscle blotch easily. Tell your dentist or other doctors in front of you have any online pharmacies of canada medical procedure. Bonus cuts with prevail upon bandages and get to the doctor claim away. In an emergency, you’ll travel platelets to restore what you helpless and Vitamin K to help your blood clot.
    Updated: August 7, 2019 15:14