Jeffs at SQL Team is my hero.
What do you do with something like this? It was found because of it broke a dump to a text file for moving to a different database. You end up with this.\
"foo
foo"
Those non-printing characters, or squares, cause all kinds of problems if you're not expecting them.
I have no control over what front-end people are using to get this into my database, or do I...
A check constraint would have stopped these nefarious character codes dead in their tracks, and will certainly put the axe on any more sneaking into my database. How do I express that I just don't want certain characters in my database?
Better get some help here. SQL Books Online, check constraints, use the like operator, ranges, got it. Wait. Don't go it.
SQLTeam.com to the rescue. Find a victim, er, helpful pal to make friends with. Great. Write a question, check. Get a response. Fine. http://weblogs.sqlteam.com/jeffs/archive/2007/04/05/check-constraints.aspx
Here's the rub though. Little did I know that a "Range" in the "Like" operator uses character attributes. If you've done any work with System.Char in the .Net framework, it will clearly show what I mean.
Final solution - Check Column Not Like '%[ -~a-z0-9]%' -- three ranges, symbols, letters, numbers.
This reminded me of some certain methods in the System.Char structure.
You know, the ones like, IsLetter, IsCharacter, IsNumber, IsSymbol...
So, says I, why not run down the list and show those...
const char sepchar = ',';
for (int i = 0; i < 256; i++)
{
b.Append(Char.IsControl(c)); b.Append(sepchar);
b.Append(Char.IsDigit(c)); b.Append(sepchar);
b.Append(Char.IsHighSurrogate(c)); b.Append(sepchar);
b.Append(Char.IsLetter(c)); b.Append(sepchar);
b.Append(Char.IsLetterOrDigit(c)); b.Append(sepchar);
b.Append(Char.IsLower(c)); b.Append(sepchar);
b.Append(Char.IsLowSurrogate(c)); b.Append(sepchar);
b.Append(Char.IsNumber(c)); b.Append(sepchar);
b.Append(Char.IsPunctuation(c)); b.Append(sepchar);
b.Append(Char.IsSeparator(c)); b.Append(sepchar);
b.Append(Char.IsSurrogate(c)); b.Append(sepchar);
b.Append(Char.IsSymbol(c)); b.Append(sepchar);
b.Append(Char.IsUpper(c)); b.Append(sepchar);
b.Append(Char.IsWhiteSpace(c));
Console.WriteLine(b.ToString());
}
But, thats kinda lame. Why not flex those programmer muscles, and prove we know some stuff. It's not always more efficient, but it's certainly elegant, and impresses the kids. Read the comments. It's all good stuff.
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;
namespace CharAttributes
{
class Program
{
static void Main(string[] args)
{
char objTest = new char();
Type objType = objTest.GetType();
const char sepchar = ',';
_MemberInfo[] arrayIMemberInfo;
#region nonPrintingNames
string[] nonPrintingNames = new string[]{
"(null)",
"(start of heading)",
"(start of text)",
"(end of text)",
"(end of transmission)",
"(enquiry)",
"(acknowledge)",
"(bell)",
"(backspace)",
"(horizontal tab)",
"(ML line feed; new line)",
"(vertical tab)",
"(NP form feed; new page)",
"(carriage return)",
"(shift out)",
"(shift in)",
"(data link escape)",
"(device control 1)",
"(device control 2)",
"(device control 3)",
"(device control 4)",
"(negative acknowledge)",
"(synchronous idle)",
"(end of trans. block)",
"(cancel)",
"(end of medium)",
"(substitute)",
"(escape)",
"(file separator)",
"(group separator)",
"(record separator)",
"(unit separator)",
};
#endregion
try
{
//Find all static or public methods in the Object class that match the specified searchCriteria.
// In our case, we want all of the public static instance methods of the Char struct
// We're also going to use the DeletgateToSearchCriteria delegate to do some additonal filtering
arrayIMemberInfo = objType.FindMembers(MemberTypes.Method,
BindingFlags.Public | BindingFlags.Static | BindingFlags.Instance,
new MemberFilter(DelegateToSearchCriteria), null);
int methodCount = arrayIMemberInfo.Length;
// Build the header
StringBuilder header = new StringBuilder();
// Append first two rows
header.Append("value"); header.Append(",");
header.Append("character"); header.Append(",");
int arrayLen = arrayIMemberInfo.Length;
// Append the names of the Methods
for (int i = 0; i < arrayLen; i++)
{
header.Append(arrayIMemberInfo[i].Name);
if (i != arrayLen - 1)
{
header.Append(",");
}
}
// Print the headers
Console.WriteLine(header.ToString());
// Use the methods we found earlier and
// invoke them on the range of ASCII characters
for (int i = 0; i < 256; i++)
{
StringBuilder line = new StringBuilder();
// append the value
line.Append(i); line.Append(sepchar);
// append the actual character
if (i < 32)
line.Append(nonPrintingNames[i]);
else
line.Append((char)i);
line.Append(sepchar);
for (int j = 0; j < methodCount; j++)
{
MethodInfo m = (MethodInfo)arrayIMemberInfo[j];
line.Append((bool)m.Invoke(null, new object[1] { (char)i }));
if (j != methodCount - 1)
{
line.Append(",");
}
}
Console.WriteLine(line.ToString());
}
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e.ToString());
}
}
public static bool DelegateToSearchCriteria(MemberInfo objMemberInfo, Object objSearch)
{
MethodInfo m = (MethodInfo)objMemberInfo;
ParameterInfo[] p = m.GetParameters();
if (p.Length == 1) // one and only one parameter
{
if ((p[0].ParameterType == Type.GetType("System.Char")) && // parameter of type System.Char
Regex.IsMatch(m.Name, "^Is") && // Method name begins with Is
m.ReturnType == Type.GetType("System.Boolean")) // Returns a System.Boolean
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
}
}