How to decode html encoded text in sql server? (or ms access!)

Question

I have a column with text in the following format...

sweet shop

is there a way to convert this directly to it's corresponding text in sql server? (it is actually a linked ms access database so I could also use access too!)

(I think this format is also called Numeric character reference and contains the code points of unicode characters)

Answer 1

Alex K is 99.99% correct, however the conversion would fail if you had Named Codes like   or £

So, here we perform a brute force replace, and then parse the string via XML

Example

Declare @S nvarchar(max) = 'sweet shop £'

Select @S = replace(@S,MapFrom,MapTo)
 From  ( values
        ('&quot;','"'),('&amp;','&'),('&apos;',''''),('&lt;','<'),('&gt;','>'),('&nbsp;',' '),('&iexcl;','¡'),
        ('&cent;','¢'),('&pound;','£'),('&curren;','¤'),('&yen;','¥'),('&brvbar;','¦'),('&sect;','§'),('&uml;','¨'),
        ('&copy;','©'),('&ordf;','ª'),('&laquo;','«'),('&not;','¬'),('&reg;','®'),('&macr;','¯'),('&deg;','°'),
        ('&plusmn;','±'),('&sup2;','²'),('&sup3;','³'),('&acute;','´'),('&micro;','µ'),('&para;','¶'),('&middot;','·'),
        ('&cedil;','¸'),('&sup1;','¹'),('&ordm;','º'),('&raquo;','»'),('&frac14;','¼'),('&frac12;','½'),('&frac34;','¾'),
        ('&iquest;','¿'),('&Agrave;','À'),('&Aacute;','Á'),('&Acirc;','Â'),('&Atilde;','Ã'),('&Auml;','Ä'),('&Aring;','Å'),
        ('&AElig;','Æ'),('&Ccedil;','Ç'),('&Egrave;','È'),('&Eacute;','É'),('&Ecirc;','Ê'),('&Euml;','Ë'),('&Igrave;','Ì'),
        ('&Iacute;','Í'),('&Icirc;','Î'),('&Iuml;','Ï'),('&ETH;','Ð'),('&Ntilde;','Ñ'),('&Ograve;','Ò'),('&Oacute;','Ó'),
        ('&Ocirc;','Ô'),('&Otilde;','Õ'),('&Ouml;','Ö'),('&times;','×'),('&Oslash;','Ø'),('&Ugrave;','Ù'),('&Uacute;','Ú'),
        ('&Ucirc;','Û'),('&Uuml;','Ü'),('&Yacute;','Ý'),('&THORN;','Þ'),('&szlig;','ß'),('&agrave;','à'),('&aacute;','á'),
        ('&;','â'),('&atilde;','ã'),('&auml;','ä'),('&aring;','å'),('&aelig;','æ'),('&ccedil;','ç'),('&egrave;','è'),
        ('&eacute;','é'),('&ecirc;','ê'),('&euml;','ë'),('&igrave;','ì'),('&iacute;','í'),('&icirc;','î'),('&iuml;','ï'),
        ('&eth;','ð'),('&ntilde;','ñ'),('&ograve;','ò'),('&oacute;','ó'),('&ocirc;','ô'),('&otilde;','õ'),('&ouml;','ö'),
        ('&divide;','÷'),('&oslash;','ø'),('&ugrave;','ù'),('&uacute;','ú'),('&ucirc;','û'),('&uuml;','ü'),('&yacute;','ý'),
        ('&thorn;','þ'),('&yuml;','ÿ'),('&amp;','&'),('&deg;','°'),('&infin;','∞'),('&permil;','‰'),('&sdot;','⋅'),
        ('&plusmn;','±'),('&dagger;','†'),('&mdash;','—'),('&not;','¬'),('&micro;','µ'),('&euro;','€'),('&pound;','£'),
        ('&yen;','¥'),('&cent;','¢'),('&euro;','€'),('&pound;','£'),('&yen;','¥'),('&cent;','¢')
       ) A (MapFrom,MapTo)

Select cast(cast(@S as xml) as nvarchar(max))

Returns

sweet shop £  -- added a space ( ) and a Pound (£) symbol to the original string

Answer 2

Here is a much easier way to decode HTML-encoded strings:

It requires SQLHTTP which is a free database/assembly that we created which you can find on our website at: http://sqlhttp.net/documentation/encoding/htmldecode/

SELECT SQLHTTP.net.HtmlDecode('sweet shop')

and the result is as expected:

-------------
sweet shop

(1 row affected)