+ (Add)
Adds
two numbers. This addition arithmetic can also add a number, in days,
to a date.
Syntax
expression + expression
Arguments
expression
Is any valid expression of any of the data types
in the numeric category.
Examples
A. Use the addition
[Field
1] + [Field 2]
[Field 1] + 100
B. Use
the addition to add days to date and time values
[Birth
Date] + 7  add 7 days to birth date
+ (String Concatenation )
An
in a string expression that concatenates two or more character or binary strings,
fields, or a combination of strings and field names into one expression (a
string ).
Syntax
expression + expression
Arguments
expression
Is any valid expression of any of the data types
in the character and binary data type category, except the image, ntext, or text data types. Both expressions
must be of the same data type, or one expression must be able to be
implicitly converted to the data type of the other expression.
An explicit conversion (CONVERT or CAST) to
character data must be used when concatenating binary strings and any characters
between the binary strings.
Examples
A. Use string concatenation
'Addr1
= ' + [Address 1] + ', Addr2 = ' + [Address 2]
B. Combine string and
date data types
'Birth
date = ' + CAST( [Birth Date] as
varchar(64) )
 (Subtract )
Subtracts
two numbers. This subtraction
arithmetic can also subtract a number, in days, from a date.
Syntax
expression  expression
Arguments
expression
Is any valid expression of any of the data types
of the numeric data type category.
Examples
A. Use the substraction
[Field
1]  [Field 2]
[Field 1]  100
Parentheses can
be used to change the order of execution. Calculations inside parentheses
are evaluated first. If parentheses are nested, the most deeply nested calculation
has precedence.
B. Use date subtraction
This example subtracts a number of days from
a datetime date.
[Birth
Date]  7  subtract 7 days from birth date
(Comment )
Indicates
userprovided text. Comments can be inserted on a separate line,
nested ( only) at the end of a line.
Syntax
 text_of_comment
Arguments
text_of_comment
Is the character string containing the text of the
comment.
Remarks
Use  for singleline
or nested comments. Comments inserted with  are delimited by the newline
character.
There is no maximum length for comments.
Examples
[Field 1] + [Field 2]  this is comment
* (Multiply
)
Multiplies
two expressions (an arithmetic multiplication ).
Syntax
expression * expression
Arguments
expression
Is any valid expression of any of the data types
of the numeric data type category except the datetime
or smalldatetime data types.
Examples
[Field 1] * [Field 2]
[Field 1] * 100
/ (Divide
)
Divides
one number by another (an arithmetic division ).
Syntax
dividend / divisor
Arguments
dividend
Is the numeric expression to divide. dividend can be any valid expression of any of
the data types of the numeric data type category except the datetime
and smalldatetime data types.
divisor
Is the numeric expression to divide the dividend by. divisor
can be any valid SQL Server expression of any of the data types of the
numeric data type category except the datetime
and smalldatetime data types.
If an integer dividend is divided by an integer divisor, the
result is an integer that has any fractional part of the result truncated.
Remarks
The actual value
returned by the / is the quotient of the first expression divided
by the second expression.
Examples
[Field 1] / [Field 2]
[Field 1] / 100
/*...*/
(Comment )
Indicates
userprovided text. The text between the /* and */ commenting
characters is not evaluated.
Syntax
/ * text_of_comment * /
Arguments
text_of_comment
Is the character string(s) containing the text of
the comment.
Remarks
Comments can be inserted on a separate line or within a formula.
Multipleline comments must be indicated by /* and */. A stylistic
convention often used for multipleline comments is to begin the first line
with /*, subsequent lines with **, and end with */.
There is no maximum length for comments.
Examples
SUBSTRING( 'abcdefg', /*this is comment*/3, 3 )
% (Modulo )
Provides
the remainder of one number divided by another.
Syntax
dividend % divisor
Arguments
dividend
Is the numeric expression to divide. dividend must be any valid expression of the integer
data type category. (A modulo is the integer that
remains after two integers are divided.)
divisor
Is the numeric expression to divide the dividend by. divisor
must be any valid expression of any of the data types of the integer data
type category.
Result Types
int
Examples
121
% 100  result is 21
DATEDIFF( month, [Birth Date], getdate() ) / 12  calculate age in years
DATEDIFF( month, [Birth Date], getdate() ) % 12  and months
& (Bitwise AND )
Performs a bitwise logical AND operation between
two integer values.
Syntax
expression & expression
Arguments
expression
Is any valid expression of any of the data types
of the integer data type category. expression
is an integer parameter that is treated and transformed into a binary
number for the bitwise operation.
Remarks
The bitwise & performs a bitwise logical AND between
the two expressions, taking each corresponding bit for both expressions. The
bits in the result are set to 1 if and only if both bits (for the current
bit being resolved) in the input expressions have a value of 1; otherwise,
the bit in the result is set to 0.
The & bitwise can be used only on expressions
of the integer data type category.
Examples
[Field
1] & [Field 2]
[Field 1] & 0x0F
[Field 1] & 10001101
 (Bitwise OR )
Performs a bitwise logical OR operation between two
given integer values.
Syntax
expression  expression
Arguments
expression
Is any valid expression of any of the data types
of the integer data type category. expression
is an integer that is treated and transformed into a binary number for
the bitwise operation.
Remarks
The bitwise
 performs a bitwise logical OR between the two expressions, taking
each corresponding bit for both expressions. The bits in the result are set
to 1 if either or both bits (for the current bit being resolved) in the input
expressions have a value of 1; if neither bit in the input expressions is
1, the bit in the result is set to 0.
The  bitwise requires two expressions, and it can be used on expressions
of only the integer data type category.
^ (Bitwise Exclusive OR )
Performs a bitwise exclusive OR operation between
two given integer values.
Syntax
expression ^ expression
Arguments
expression
Is any valid expression of any of the data types
of the integer data type category. expression
is an integer that is treated and transformed into a binary number for
the bitwise operation.
Remarks
The bitwise
^ performs a bitwise logical ^ between the two expressions, taking
each corresponding bit for both expressions. The bits in the result are set
to 1 if either (but not both) bits (for the current bit being resolved) in
the input expressions have a value of 1; if both bits are either a value of
0 or 1, the bit in the result is cleared to a value of 0.
Examples
[Field
1] ^ [Field 2]
[Field 1] ^ 0x0F
[Field 1] ^ 10001101
~ (Bitwise NOT)
Performs
a bitwise logical NOT operation for one given integer value.
Syntax
~ expression
Arguments
expression
Is any valid expression of any of the
data types of the integer data type category. expression
is an integer that is treated and transformed into a binary number for
the bitwise operation.
Remarks
The bitwise
~ performs a bitwise logical NOT for the expression, taking
each corresponding bit. The bits in the result are set to 1 if one bit (for
the current bit being resolved) in expression has a value of 0; otherwise,
the bit in the result is cleared to a value of 1.
Examples
~
[Field 1]
AND
Combines
two Boolean expressions and returns TRUE when both expressions are TRUE. When
more than one logical is used in a statement, AND s are evaluated
first. You can change the order of evaluation by using parentheses.
Syntax
boolean_expression AND boolean_expression
Examples
IF ( [Title] = 'CEO' AND [State] = 'NY' )
THEN
[Salutation] + ' is Chief Executive Officer'
ELSE
[Salutation] + '
is ' + [Title]
END
BETWEEN
Specifies a range
to test.
Syntax
test_expression [
NOT ] BETWEEN begin_expression AND
end_expression
Arguments
test_expression
Is the expression to test for in the range defined
by begin_expression and end_expression.
test_expression
must be the same data type as both begin_expression
and end_expression.
NOT
Specifies that the result of the predicate be negated.
begin_expression
Is any valid expression. begin_expression
must be the same data type as both test_expression
and end_expression.
end_expression
Is any valid expression. end_expression
must be the same data type as both test_expression
and begin_expression.
AND
Acts as a placeholder indicating that test_expression
should be within the range indicated by begin_expression
and end_expression.
Result Types
Boolean
Result Value
BETWEEN returns TRUE if the value of test_expression
is greater than or equal to the value of begin_expression
and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression
is less than the value of begin_expression
or greater than the value of end_expression.
Remarks
To specify
an exclusive range, use the greater than (>) and less than s (<).
If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is
UNKNOWN.
Examples
A. Use BETWEEN
IF ( [Field 1] BETWEEN 4095 AND 12000 )
THEN
[Field 2] * 25/100  25 percents
ELSE
[Field 2] * 30/100  30 percents
END
B. Use
> and < instead of BETWEEN
IF ( [Field 1] > 4095 AND [Field
1] < 12000 )
THEN
[Field 2] * 25/100  25 percents
ELSE
[Field 2] * 30/100  30 percents
END
C. Use NOT BETWEEN
IF ( [Field 1] NOT BETWEEN 4095 AND 12000 )
THEN
[Field 2] * 30/100  30 percents
ELSE
[Field 2] * 25/100  25 percents
END
D. Use
BETWEEN with dates
IF( [Birth Date] BETWEEN '05/15/60' AND '05/28/70')
THEN
[Salutation] + '
 birth date was in sixties'
ELSE
[Salutation]
END
IN
Determines
if a given value matches any value in a list.
Syntax
test_expression [
NOT ] IN
(
expression [ ,...n
]
)
Arguments
test_expression
Is any valid expression.
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of the
same type as test_expression.
Result Types
Boolean
Result Value
If the value
of test_expression is equal to any
expression from the commaseparated list, the result value is TRUE.
Otherwise, the result value is FALSE.
Using NOT IN negates the returned value.
Examples
A. Using IN
IF( [State] IN ('NY','NJ','CA','IN','MD') )
THEN
[State] + '
 type 1'
ELSE
[State] + '
 type 2'
END
B. Using NOT IN
IF( [State] NOT IN ('NY','NJ','CA','IN','MD') )
THEN
[State] + '
 type 2'
ELSE
[State] + '
 type 1'
END
LIKE
Determines
whether or not a given character string matches a specified pattern. A pattern
can include regular characters and wildcard characters. During pattern matching,
regular characters must exactly match the characters specified in the character
string. Wildcard characters, however, can be matched with arbitrary fragments
of the character string. Using wildcard characters makes the LIKE
more flexible than using the = and != string comparison
s.
Syntax
match_expression [
NOT ] LIKE pattern [ ESCAPE escape_character ]
Arguments
match_expression
Is any valid expression of character string data
type.
pattern
Is the pattern to search for in match_expression,
and can include these valid SQL Server wildcard characters.
Wildcard character 
Description 
Example 

% 
Any string of zero or more characters. 
IF( [Contact]
LIKE 'J%' ) true if contact name starts with 'J' 

_ (underscore) 
Any single
character. 
IF( [First
Name]
LIKE '_ean' )true for all fourletter
first names that end with ean (Dean, Sean,
and so on). 

[ ] 
Any single character within the specified range ([af]) or set
([abcdef]). 
IF( [Last
Name] LIKE '[CP]arsen' ) true for all last names ending
with arsen and beginning with any single character
between C and P, for example Carsen, Larsen,
Karsen, and so on. 

[^] 
Any single character not within the specified range ([^af]) or
set ([^abcdef]). 
IF( [Last
Name] LIKE 'de[^l]%' ) true for all last names beginning with de and where the following
letter is not l. 

escape_character
Is any valid expression of any of the data types of the character string data
type category. escape_character
has no default and must consist of only one character.
Result Types
Boolean
Result Value
LIKE returns TRUE if the match_expression matches the specified pattern.
Remarks
When you perform
string comparisons with LIKE, all characters in the pattern string are significant,
including leading or trailing spaces. Trailing blanks, in the expression to
which the pattern is matched, are ignored.
Using Wildcard Characters as Literals
You can use
the wildcard pattern matching characters as literal characters. To use a wildcard
character as a literal character, enclose the wildcard character in brackets.
The table shows several examples of using the LIKE keyword and the [ ] wildcard
characters.
Symbol 
Meaning 

LIKE '5[%]' 
5% 

LIKE '[_]n' 
_n 

LIKE '[acdf]' 
a, b,
c, d, or f 

LIKE '[acdf]' 
, a, c, d, or
f 

LIKE '[ [ ]' 
[ 

LIKE ']' 
] 

LIKE 'abc[_]d%' 
abc_d and abc_de 

LIKE 'abc[def]' 
abcd, abce,
and abcf 

Pattern Matching with the ESCAPE Clause
To search for
the percent sign as a character instead of as a wildcard character, the ESCAPE
keyword and escape character must be provided
This example shows how to compare field with the string "50% off when
100 or more copies are purchased".
IF( [Field1] LIKE '50%% off when 100 or more copies are purchased' ESCAPE '%' )
THEN
[Field2]
ELSE
[Field3]
END
Examples
A. Use LIKE with the % wildcard character
IF( [Phone] LIKE '415%'
)  true for all phone numbers that have area code 415
THEN
[Field1]
ELSE
[Field2]
END
B. Use NOT LIKE with the % wildcard character
IF( [Phone] NOT LIKE '415%' )
THEN
[Field2]
ELSE
[Field1]
END
C. Use
the ESCAPE clause
This example uses the ESCAPE clause and the escape character to
compare Discount field with the exact character string 1015%
IF( [Discount] LIKE '%1015!%
off%' ESCAPE '!'
)
THEN
[Field1]
ELSE
[Field2]
END
D. Use
the [ ] wildcard characters
CASE
 true if first name is Cheryl or Sheryl
WHEN [First Name] LIKE '[CS]heryl' THEN 'Contact ' + [Contact] + ',
type 1'
 true if last name is Carson, Carsen, Karson,
or Karsen
WHEN [Last Name] LIKE '[CK]ars[eo]n' THEN 'Contact
' + [Contact] + ', type 2'
 true
if first name ends with ean (Dean, Sean, and so
on)
WHEN [First Name] LIKE '_ean' THEN 'Contact
' + [Contact] + ', type 3'
ELSE 'Unsupported contact type'
END
NOT
Negates
a Boolean input.
Syntax
[ NOT ] boolean_expression
Arguments
boolean_expression
Is any valid Boolean expression.
Result Types
Boolean
Result Value
NOT reverses the value of any Boolean expression.
OR
Combines
two conditions. When more
than one logical is used in a statement, OR s are evaluated
after AND s. However, you can change the order of evaluation by using
parentheses.
Syntax
boolean_expression OR boolean_expression
Arguments
boolean_expression
Is any valid expression that returns TRUE, FALSE,
or UNKNOWN.
Result Types
Boolean
Result Value
OR returns TRUE when either of the conditions is TRUE.
Examples
IF ( [Title] = 'CEO' OR [State] = 'NY' )
THEN
[Salutation] + ' is Chief Executive Officer'
ELSE
[Salutation] + ' is ' + [Title]
END