Category Archives: T-SQL

HtmlDecode inside SQLCLR without System.Web reference

I grabbed code from the WebUtility class which is available in System.Net of the .NET Framework 4.0. Can be compiled inside .NET 2.0.

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
namespace System.Net
{
public static class WebUtility
{
private static class HtmlEntities
{
private static string[] _entitiesList = new string[]
{
""-quot",
"&-amp",
"'-apos",
"<-lt>-gt",
"u00a0-nbsp",
"¡-iexcl",
"¢-cent",
"£-pound",
"¤-curren",
"¥-yen",
"¦-brvbar",
"§-sect",
"¨-uml",
"©-copy",
"ª-ordf",
"«-laquo",
"¬-not",
"­-shy",
"®-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",
"â-acirc",
"ã-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",
"Œ-OElig",
"œ-oelig",
"Š-Scaron",
"š-scaron",
"Ÿ-Yuml",
"ƒ-fnof",
"ˆ-circ",
"˜-tilde",
"Α-Alpha",
"Β-Beta",
"Γ-Gamma",
"Δ-Delta",
"Ε-Epsilon",
"Ζ-Zeta",
"Η-Eta",
"Θ-Theta",
"Ι-Iota",
"Κ-Kappa",
"Λ-Lambda",
"Μ-Mu",
"Ν-Nu",
"Ξ-Xi",
"Ο-Omicron",
"Π-Pi",
"Ρ-Rho",
"Σ-Sigma",
"Τ-Tau",
"Υ-Upsilon",
"Φ-Phi",
"Χ-Chi",
"Ψ-Psi",
"Ω-Omega",
"α-alpha",
"β-beta",
"γ-gamma",
"δ-delta",
"ε-epsilon",
"ζ-zeta",
"η-eta",
"θ-theta",
"ι-iota",
"κ-kappa",
"λ-lambda",
"μ-mu",
"ν-nu",
"ξ-xi",
"ο-omicron",
"π-pi",
"ρ-rho",
"ς-sigmaf",
"σ-sigma",
"τ-tau",
"υ-upsilon",
"φ-phi",
"χ-chi",
"ψ-psi",
"ω-omega",
"ϑ-thetasym",
"ϒ-upsih",
"ϖ-piv",
"u2002-ensp",
"u2003-emsp",
"u2009-thinsp",
"‌-zwnj",
"‍-zwj",
"‎-lrm",
"‏-rlm",
"–-ndash",
"—-mdash",
"‘-lsquo",
"’-rsquo",
"‚-sbquo",
"“-ldquo",
"”-rdquo",
"„-bdquo",
"†-dagger",
"‡-Dagger",
"•-bull",
"…-hellip",
"‰-permil",
"′-prime",
"″-Prime",
"‹-lsaquo",
"›-rsaquo",
"‾-oline",
"⁄-frasl",
"€-euro",
"ℑ-image",
"℘-weierp",
"ℜ-real",
"™-trade",
"ℵ-alefsym",
"←-larr",
"↑-uarr",
"→-rarr",
"↓-darr",
"↔-harr",
"↵-crarr",
"⇐-lArr",
"⇑-uArr",
"⇒-rArr",
"⇓-dArr",
"⇔-hArr",
"∀-forall",
"∂-part",
"∃-exist",
"∅-empty",
"∇-nabla",
"∈-isin",
"∉-notin",
"∋-ni",
"∏-prod",
"∑-sum",
"−-minus",
"∗-lowast",
"√-radic",
"∝-prop",
"∞-infin",
"∠-ang",
"∧-and",
"∨-or",
"∩-cap",
"∪-cup",
"∫-int",
"∴-there4",
"∼-sim",
"≅-cong",
"≈-asymp",
"≠-ne",
"≡-equiv",
"≤-le",
"≥-ge",
"⊂-sub",
"⊃-sup",
"⊄-nsub",
"⊆-sube",
"⊇-supe",
"⊕-oplus",
"⊗-otimes",
"⊥-perp",
"⋅-sdot",
"⌈-lceil",
"⌉-rceil",
"⌊-lfloor",
"⌋-rfloor",
"〈-lang",
"〉-rang",
"◊-loz",
"♠-spades",
"♣-clubs",
"♥-hearts",
"♦-diams"
};
private static Dictionary _lookupTable = WebUtility.HtmlEntities.GenerateLookupTable();
private static Dictionary GenerateLookupTable()
{
Dictionary dictionary = new Dictionary(StringComparer.Ordinal);
string[] entitiesList = WebUtility.HtmlEntities._entitiesList;
for (int i = 0; i < entitiesList.Length; i++)
{
string text = entitiesList[i];
dictionary.Add(text.Substring(2), text[0]);
}
return dictionary;
}
public static char Lookup(string entity)
{
char result;
WebUtility.HtmlEntities._lookupTable.TryGetValue(entity, out result);
return result;
}
}
private static char[] _htmlEntityEndingChars = new char[]
{
';',
'&'
};
public static string HtmlDecode(string value)
{
if (string.IsNullOrEmpty(value))
{
return value;
}
if (value.IndexOf('&') < 0)
{
return value;
}
StringWriter stringWriter = new StringWriter(CultureInfo.InvariantCulture);
WebUtility.HtmlDecode(value, stringWriter);
return stringWriter.ToString();
}
public static void HtmlDecode(string value, TextWriter output)
{
if (value == null)
{
return;
}
if (output == null)
{
throw new ArgumentNullException("output");
}
if (value.IndexOf('&') < 0)
{
output.Write(value);
return;
}
int length = value.Length;
int i = 0;
while (i < length)
{
char c = value[i];
if (c != '&')
{
goto IL_110;
}
int num = value.IndexOfAny(WebUtility._htmlEntityEndingChars, i + 1);
if (num <= 0 || value[num] != ';')
{
goto IL_110;
}
string text = value.Substring(i + 1, num - i - 1);
if (text.Length > 1 && text[0] == '#')
{
ushort num2;
if (text[1] == 'x' || text[1] == 'X')
{
ushort.TryParse(text.Substring(2), NumberStyles.AllowHexSpecifier, NumberFormatInfo.InvariantInfo, out num2);
}
else
{
ushort.TryParse(text.Substring(1), NumberStyles.Integer, NumberFormatInfo.InvariantInfo, out num2);
}
if (num2 != 0)
{
c = (char)num2;
i = num;
goto IL_110;
}
goto IL_110;
}
else
{
i = num;
char c2 = WebUtility.HtmlEntities.Lookup(text);
if (c2 != '')
{
c = c2;
goto IL_110;
}
output.Write('&');
output.Write(text);
output.Write(';');
}
IL_117:
i++;
continue;
IL_110:
output.Write(c);
goto IL_117;
}
}
}
}

Check whether text is UPPER case in T-SQL

I have prepared small snippet which will help you check whether some text is UPPER case in T-SQL. I have not found any good example while Googling. You can easily update to code to check whether the text is lower case by replacing UPPER with LOWER in the snippet.

DECLARE @Name AS NVARCHAR(64)
SET @Name = 'NAME'

IF BINARY_CHECKSUM(@Name) = BINARY_CHECKSUM(UPPER(@Name))
SELECT 1;
ELSE
SELECT 0;