The following operators, operations, and functions can be used in custom calculation fields.
Operators
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When creating comparison expressions, the following operators are allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in the Evaluation Clause:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
String Operators
To concatenate a string, use the + character. Whether string comparisons are case-sensitive or not is determined by the value of the DataSet class's CaseSensitive property. However, you can override that value with the DataTable class's CaseSensitive property.
Functions
The following functions are also supported by custom calculation fields:
CONVERT
Description
|
Converts given expression to a specified .NET Framework Type (see end of document for comparison of SQL Server data types to .NET Framework data types).
|
Syntax
|
Convert(expression, type)
|
Arguments
|
expression-- The expression to convert.
type-- The .NET Framework type to which the value will be converted.
|
Example:
myDataColumn.Expression="Convert(total, 'System.Int32')".
Use the syntax ‘System.datatypename’ in front of the name of the data type you are converting to. The data type must be enclosed in tics.
All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only.
Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.
See the end of this document for a list of SQL Server data types and their corresponding .NET Framework data types.
LEN
Description
|
Gets the length of a string
|
Syntax
|
LEN(expression)
|
Arguments
|
expression-- The string to be evaluated.
|
Example:
myDataColumn.Expression="Len(ItemName)"
ISNULL
Description
|
Checks an expression and either returns the checked expression or a replacement value.
|
Syntax
|
ISNULL(expression, replacementvalue)
|
Arguments
|
expression-- The expression to check.
replacementvalue-- If expression is a null reference (Nothing), replacementvalue is returned.
|
Example:
myDataColumn.Expression="IsNull(price, -1)"
IIF
Description
|
Gets one of two values depending on the result of a logical expression.
|
Syntax
|
IIF(expr, truepart, falsepart)
|
Arguments
|
expr-- The expression to evaluate.
truepart-- The value to return if the expression is true.
falsepart-- The value to return if the expression is false.
|
Example:
myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')
TRIM
Description
|
Removes all leading and trailing blank characters like\r,\n,\t, ' '
|
Syntax
|
TRIM(expression)
|
Arguments
|
expression-- The expression to trim.
|
SUBSTRING
Description
|
Gets a sub-string of a specified length, starting at a specified point in the string.
|
Syntax
|
SUBSTRING(expression, start,
length)
|
Arguments
|
expression-- The source string for the substring.
start-- Integer that specifies where the substring begins.
length-- Integer that specifies the length of the substring.
|
Example:
myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
Comparison of SQL Server and .NET Framework data types
Refer to the table below to see the corresponding .NET Framework types of SQL Server Data Types supported by custom calculation fields.
SQL Server Data Type
|
.NET Framework type
|
Bigint
|
Int64
|
Binary
|
Byte[]
|
Bit
|
Boolean
|
Char
|
String Char[]
|
Datetime
|
DateTime
|
Decimal
|
Decimal
|
Float
|
Double
|
Image
|
Byte[]
|
Int
|
Int32
|
Money
|
Decimal
|
Nchar
|
String Char[]
|
Ntext
|
String Char[]
|
Numeric
|
Decimal
|
nvarchar
|
String Char[]
|
real
|
Single
|
smalldatetime
|
DateTime
|
smallint
|
Int16
|
smallmoney
|
Decimal
|
sql_variant
|
Object *
|
text
|
String Char[]
|
timestamp
|
Byte[]
|
tinyint
|
Byte
|
uniqueidentifier
|
Guid
|
varbinary
|
Byte[]
|
varchar
|
String Char[]
|