.NET DataTable Select Experssions

Sometimes I like to get raw data from a database table and select information from it through .NET code. I use the SELECT() method and pass it an expression. The problem is I never remember what types of expressions are valid. So I am listing them here as I come across them. One thing to remeber. If your column names have spaces, use brackets around the column name.

*Column name with spaces
string filter = "[job grade] = '" + jobGradeRowsToUpDate[j]["JobGrade"].ToString() + "'";

* NOT IN (item 1,item 2,...)
DataRow[] jobGradeRowsToDelete = currentJobGradeData.Select("jobgrade NOT IN (" + newJobGradeID + ")");

* not equal <>
DataRow[] jobGradeRowsToDelete = currentJobGradeData.Select("jobgrade <> '1'");

Other
Expression Operators
The list of supported operators is somewhat small:

Math Operators:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus, the remainder after division)
String Operator:

+ (concatenation, joining strings)
Comparison Operators:

< (less than) > (greater than)
<= (less than or equal to) >= (greater than or equal to)
<> (not equal to)
= (equal)
IN (compare to a list of items)
LIKE (pattern matching)
Scalar Functions:

Convert (change from one data type to another)
Len (return the length of the string)
IsNull (test for null)
IIf (Immediate If)
Trim (trim white space from string)
Substring (return a part of a string)
Aggregation Functions:

Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Standard deviation)
Var (Statistical variance)

**Source: http://home.hot.rr.com/graye/Articles/ADO_Expressions.htm