Operators

Transact-SQL Reference

Transact-SQL Reference

Operators

An operator is a symbol specifying an action that is performed on one or more expressions. Microsoft® SQL Server™ 2000 uses these operator categories:

  • Arithmetic operators

  • Assignment operator

  • Bitwise operators

  • Comparison operators

  • Logical operators

  • String concatenation operator

  • Unary operators
Arithmetic Operators

Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category. For more information about data type categories, see Transact-SQL Syntax Conventions.

Operator Meaning
+ (Add) Addition.
- (Subtract) Subtraction.
* (Multiply) Multiplication.
/ (Divide) Division.
% (Modulo) Returns the integer remainder of a division. For example, 12 % 5 = 2 because the remainder of 12 divided by 5 is 2.

The plus (+) and minus (-) can also be used to perform arithmetic operations on datetime and smalldatetime values.

For more information about the precision and scale of the result of an arithmetic operation, see Precision, Scale, and Length.

Assignment Operator

Transact-SQL has one assignment operator, the equals sign (=). In this example, the @MyCounter variable is created. Then, the assignment operator sets @MyCounter to a value returned by an expression.

DECLARE @MyCounter INT
SET @MyCounter = 1

The assignment operator can also be used to establish the relationship between a column heading and the expression defining the values for the column. This example displays two column headings named FirstColumnHeading and SecondColumnHeading. The string xyz is displayed in the FirstColumnHeading column heading for all rows. Then, each product ID from the Products table is listed in the SecondColumnHeading column heading.

USE Northwind
GO
SELECT FirstColumnHeading = 'xyz',
       SecondColumnHeading = ProductID
FROM Products
GO
Bitwise Operators

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.

Operator Meaning
& (Bitwise AND) Bitwise AND (two operands).
| (Bitwise OR) Bitwise OR (two operands).
^ (Bitwise Exclusive OR) Bitwise exclusive OR (two operands).

The operands for bitwise operators can be any of the data types of the integer or binary string data type categories (except for the image data type), with the exception that both operands cannot be any of the data types of the binary string data type category. The table shows the supported operand data types.

Left operand Right operand
binary int, smallint, or tinyint
bit int, smallint, tinyint, or bit
int int, smallint, tinyint, binary, or varbinary
smallint int, smallint, tinyint, binary, or varbinary
tinyint int, smallint, tinyint, binary, or varbinary
varbinary int, smallint, or tinyint

Comparison Operators

Comparison operators test whether or not two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

Operator Meaning
= (Equals) Equal to
> (Greater Than) Greater than
< (Less Than) Less than
>= (Greater Than or Equal To) Greater than or equal to
<= (Less Than or Equal To) Less than or equal to
<> (Not Equal To) Not equal to
!= (Not Equal To) Not equal to (not SQL-92 standard)
!< (Not Less Than) Not less than (not SQL-92 standard)
!> (Not Greater Than) Not greater than (not SQL-92 standard)

The result of a comparison operator has the Boolean data type, which has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions.

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an equals operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE if SET ANSI_NULLS is OFF.

Expressions with Boolean data types are used in the WHERE clause to filter the rows that qualify for the search conditions and in control-of-flow language statements such as IF and WHILE, for example:

USE Northwind
GO
DECLARE @MyProduct int
SET @MyProduct = 10
IF (@MyProduct <> 0)
   SELECT *
   FROM Products
   WHERE ProductID = @MyProduct
GO
Logical Operators

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE or FALSE.

Operator Meaning
ALL TRUE if all of a set of comparisons are TRUE.
AND TRUE if both Boolean expressions are TRUE.
ANY TRUE if any one of a set of comparisons are TRUE.
BETWEEN TRUE if the operand is within a range.
EXISTS TRUE if a subquery contains any rows.
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern.
NOT Reverses the value of any other Boolean operator.
OR TRUE if either Boolean expression is TRUE.
SOME TRUE if some of a set of comparisons are TRUE.

For more information about logical operators, see the specific logical operator topic.

String Concatenation Operator

The string concatenation operator allows string concatenation with the addition sign (+), which is also known as the string concatenation operator. All other string manipulation is handled through string functions such as SUBSTRING.

By default, an empty string is interpreted as an empty string in INSERT or assignment statements on data of the varchar data type. In concatenating data of the varchar, char, or text data types, the empty string is interpreted as an empty string. For example, 'abc' + '' + 'def' is stored as 'abcdef'. However, if the sp_dbcmptlevel compatibility level setting is 65, empty constants are treated as a single blank character and 'abc' + '' + 'def' is stored as 'abc def'. For more information about the interpretation of empty strings, see sp_dbcmptlevel.

When two character strings are concatenated, the collation of the result expression is set following the rules of collation precedence. For more information, see Collation Precedence.

Unary Operators

Unary operators perform an operation on only one expression of any of the data types of the numeric data type category.

Operator Meaning
+ (Positive) Numeric value is positive.
- (Negative) Numeric value is negative.
~ (Bitwise NOT) Returns the ones complement of the number.

The + (Positive) and - (Negative) operators can be used on any expression of any of the data types of the numeric data type category. The ~ (Bitwise NOT) operator can be used only on expressions of any of the data types of the integer data type category.

Operator Precedence

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:

  • + (Positive), - (Negative), ~ (Bitwise NOT)

  • * (Multiply), / (Division), % (Modulo)

  • + (Add), (+ Concatenate), - (Subtract)

  • =,  >,  <,  >=,  <=,  <>,  !=,  !>,  !< (Comparison operators)

  • ^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)

  • NOT

  • AND

  • ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

  • = (Assignment)

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.

DECLARE @MyNumber int
SET @MyNumber = 4 - 2 + 27
-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber

Use parentheses to override the defined precedence of the operators in an expression. Everything within the parentheses is evaluated first to yield a single value before that value can be used by any operator outside of the parentheses.

For example, in the expression used in the SET statement of this example, the multiplication operator has a higher precedence than the addition operator, so it gets evaluated first; the expression result is 13.

DECLARE @MyNumber int
SET @MyNumber = 2 * 4 + 5
-- Evaluates to 8 + 5 which yields an expression result of 13.
SELECT @MyNumber

In the expression used in the SET statement of this example, the parentheses causes the addition to be performed first; the expression result is 18.

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + 5)
-- Evaluates to 2 * 9 which yields an expression result of 18.
SELECT @MyNumber

If an expression has nested parentheses, the most deeply nested expression is evaluated first. This example contains nested parentheses, with the expression 5 - 3 in the most deeply nested set of parentheses. This expression yields a value of 2. Then, the addition operator (+) adds this result to 4, which yields a value of 6. Finally, the 6 is multiplied by 2 to yield an expression result of 12.

DECLARE @MyNumber int
SET @MyNumber = 2 * (4 + (5 - 3) )
-- Evaluates to 2 * (4 + 2) which further evaluates to 2 * 6, and 
-- yields an expression result of 12.
SELECT @MyNumber

See Also

Functions