+ (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 user-provided 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 single-line 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 user-provided 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. Multiple-line comments must be indicated by /* and */. A stylistic convention often used for multiple-line 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 comma-separated 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 four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	IF( [Last Name] LIKE '[C-P]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 ([^a-f]) 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 '[a-cdf]'	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 10-15% 
IF( [Discount] LIKE  '%10-15!% 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