snowflake: Converting Encoding of field to UTF8

Question

I have a field from source which is having HTML encoding character, I have to load data in UTF8 format. As example: data from source is "VE-S&P Global Blue Coat Replacement" Data in Target required to be store as "VE-S&P Global Blue Coat Replacement" .

Can anybody guide me how can we convert encoding to UTF8 in Snowflake?

Answer 1

This isn't bullet-proof but if your set of HTML character entities is limited to very early versions of HTML, you can leverage one of the answers from the question "Unescape HTML entities in JavaScript" as a Snowflake JavaScript UDF after loading the source data a temporary table.

Here's a naive example incorporating the linked answer:

> CREATE OR REPLACE FUNCTION HTML_ENTITY_TO_SYMBOL(STRING VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS 
$$
  return STRING.replace(/&Agrave;/g, "À").replace(/&Aacute;/g, "Á").replace(/&Acirc;/g, "Â").replace(/&Atilde;/g, "Ã").replace(/&Auml;/g, "Ä").replace(/&Aring;/g, "Å").replace(/&agrave;/g, "à").replace(/&acirc;/g, "â").replace(/&atilde;/g, "ã").replace(/&auml;/g, "ä").replace(/&aring;/g, "å").replace(/&AElig;/g, "Æ").replace(/&aelig;/g, "æ").replace(/&szlig;/g, "ß").replace(/&Ccedil;/g, "Ç").replace(/&ccedil;/g, "ç").replace(/&Egrave;/g, "È").replace(/&Eacute;/g, "É").replace(/&Ecirc;/g, "Ê").replace(/&Euml;/g, "Ë").replace(/&egrave;/g, "è").replace(/&eacute;/g, "é").replace(/&ecirc;/g, "ê").replace(/&euml;/g, "ë").replace(/&#131;/g, "ƒ").replace(/&Igrave;/g, "Ì").replace(/&Iacute;/g, "Í").replace(/&Icirc;/g, "Î").replace(/&Iuml;/g, "Ï").replace(/&igrave;/g, "ì").replace(/&iacute;/g, "í").replace(/&icirc;/g, "î").replace(/&iuml;/g, "ï").replace(/&Ntilde;/g, "Ñ").replace(/&ntilde;/g, "ñ").replace(/&Ograve;/g, "Ò").replace(/&Oacute;/g, "Ó").replace(/&Ocirc;/g, "Ô").replace(/&Otilde;/g, "Õ").replace(/&Ouml;/g, "Ö").replace(/&ograve;/g, "ò").replace(/&oacute;/g, "ó").replace(/&ocirc;/g, "ô").replace(/&otilde;/g, "õ").replace(/&ouml;/g, "ö").replace(/&Oslash;/g, "Ø").replace(/&oslash;/g, "ø").replace(/&#140;/g, "Œ").replace(/&#156;/g, "œ").replace(/&#138;/g, "Š").replace(/&#154;/g, "š").replace(/&Ugrave;/g, "Ù").replace(/&Uacute;/g, "Ú").replace(/&Ucirc;/g, "Û").replace(/&Uuml;/g, "Ü").replace(/&ugrave;/g, "ù").replace(/&uacute;/g, "ú").replace(/&ucirc;/g, "û").replace(/&uuml;/g, "ü").replace(/&#181;/g, "µ").replace(/&#215;/g, "×").replace(/&Yacute;/g, "Ý").replace(/&#159;/g, "Ÿ").replace(/&yacute;/g, "ý").replace(/&yuml;/g, "ÿ").replace(/&#176;/g, "°").replace(/&#134;/g, "†").replace(/&#135;/g, "‡").replace(/&lt;/g, "<").replace(/&gt;/g, ">").replace(/&#177;/g, "±").replace(/&#171;/g, "«").replace(/&#187;/g, "»").replace(/&#191;/g, "¿").replace(/&#161;/g, "¡").replace(/&#183;/g, "·").replace(/&#149;/g, "•").replace(/&#153;/g, "™").replace(/&copy;/g, "©").replace(/&reg;/g, "®").replace(/&#167;/g, "§").replace(/&#182;/g, "¶").replace(/&Alpha;/g, "Α").replace(/&Beta;/g, "Β").replace(/&Gamma;/g, "Γ").replace(/&Delta;/g, "Δ").replace(/&Epsilon;/g, "Ε").replace(/&Zeta;/g, "Ζ").replace(/&Eta;/g, "Η").replace(/&Theta;/g, "Θ").replace(/&Iota;/g, "Ι").replace(/&Kappa;/g, "Κ").replace(/&Lambda;/g, "Λ").replace(/&Mu;/g, "Μ").replace(/&Nu;/g, "Ν").replace(/&Xi;/g, "Ξ").replace(/&Omicron;/g, "Ο").replace(/&Pi;/g, "Π").replace(/&Rho;/g, "Ρ").replace(/&Sigma;/g, "Σ").replace(/&Tau;/g, "Τ").replace(/&Upsilon;/g, "Υ").replace(/&Phi;/g, "Φ").replace(/&Chi;/g, "Χ").replace(/&Psi;/g, "Ψ").replace(/&Omega;/g, "Ω").replace(/&alpha;/g, "α").replace(/&beta;/g, "β").replace(/&gamma;/g, "γ").replace(/&delta;/g, "δ").replace(/&epsilon;/g, "ε").replace(/&zeta;/g, "ζ").replace(/&eta;/g, "η").replace(/&theta;/g, "θ").replace(/&iota;/g, "ι").replace(/&kappa;/g, "κ").replace(/&lambda;/g, "λ").replace(/&mu;/g, "μ").replace(/&nu;/g, "ν").replace(/&xi;/g, "ξ").replace(/&omicron;/g, "ο").replace(/&piρ;/g, "ρ").replace(/&rho;/g, "ς").replace(/&sigmaf;/g, "ς").replace(/&sigma;/g, "σ").replace(/&tau;/g, "τ").replace(/&phi;/g, "φ").replace(/&chi;/g, "χ").replace(/&psi;/g, "ψ").replace(/&omega;/g, "ω").replace(/&bull;/g, "•").replace(/&hellip;/g, "…").replace(/&prime;/g, "′").replace(/&Prime;/g, "″").replace(/&oline;/g, "‾").replace(/&frasl;/g, "⁄").replace(/&weierp;/g, "℘").replace(/&image;/g, "ℑ").replace(/&real;/g, "ℜ").replace(/&trade;/g, "™").replace(/&alefsym;/g, "ℵ").replace(/&larr;/g, "←").replace(/&uarr;/g, "↑").replace(/&rarr;/g, "→").replace(/&darr;/g, "↓").replace(/&barr;/g, "↔").replace(/&crarr;/g, "↵").replace(/&lArr;/g, "⇐").replace(/&uArr;/g, "⇑").replace(/&rArr;/g, "⇒").replace(/&dArr;/g, "⇓").replace(/&hArr;/g, "⇔").replace(/&forall;/g, "∀").replace(/&part;/g, "∂").replace(/&exist;/g, "∃").replace(/&empty;/g, "∅").replace(/&nabla;/g, "∇").replace(/&isin;/g, "∈").replace(/&notin;/g, "∉").replace(/&ni;/g, "∋").replace(/&prod;/g, "∏").replace(/&sum;/g, "∑").replace(/&minus;/g, "−").replace(/&lowast;/g, "∗").replace(/&radic;/g, "√").replace(/&prop;/g, "∝").replace(/&infin;/g, "∞").replace(/&OEig;/g, "Œ").replace(/&oelig;/g, "œ").replace(/&Yuml;/g, "Ÿ").replace(/&spades;/g, "♠").replace(/&clubs;/g, "♣").replace(/&hearts;/g, "♥").replace(/&diams;/g, "♦").replace(/&thetasym;/g, "ϑ").replace(/&upsih;/g, "ϒ").replace(/&piv;/g, "ϖ").replace(/&Scaron;/g, "Š").replace(/&scaron;/g, "š").replace(/&ang;/g, "∠").replace(/&and;/g, "∧").replace(/&or;/g, "∨").replace(/&cap;/g, "∩").replace(/&cup;/g, "∪").replace(/&int;/g, "∫").replace(/&there4;/g, "∴").replace(/&sim;/g, "∼").replace(/&cong;/g, "≅").replace(/&asymp;/g, "≈").replace(/&ne;/g, "≠").replace(/&equiv;/g, "≡").replace(/&le;/g, "≤").replace(/&ge;/g, "≥").replace(/&sub;/g, "⊂").replace(/&sup;/g, "⊃").replace(/&nsub;/g, "⊄").replace(/&sube;/g, "⊆").replace(/&supe;/g, "⊇").replace(/&oplus;/g, "⊕").replace(/&otimes;/g, "⊗").replace(/&perp;/g, "⊥").replace(/&sdot;/g, "⋅").replace(/&lcell;/g, "⌈").replace(/&rcell;/g, "⌉").replace(/&lfloor;/g, "⌊").replace(/&rfloor;/g, "⌋").replace(/&lang;/g, "⟨").replace(/&rang;/g, "⟩").replace(/&loz;/g, "◊").replace(/&#039;/g, "'").replace(/&amp;/g, "&").replace(/&quot;/g, "\"");
$$;

Testing against your sample input:

> SELECT HTML_ENTITY_TO_SYMBOL('VE-S&P Global Blue Coat Replacement');
+-------------------------------------+
| COL_1                               |
|-------------------------------------|
|"VE-S&P Global Blue Coat Replacement"|
+-------------------------------------+

Note: The function does not exhaustively cover all possible HTML character entities possible, including most of the numeric (decimal and hexadecimal) equivalent forms.

P.s. Try to clean up the data with local tools before you insert it into a data warehouse such as Snowflake. SQL and Regular Expressions are not built to handle tasks that require to accurately parse markup data and entities such as those within HTML, XML, etc. documents. Your standard programming languages with their markup libraries are more suited to that task.