Added line is this color
Deleted line is this color
+ +<h1><a name="_operators"></a>Operators (T-SQL)</h1> +<p>An operator is a symbol specifying an action that is performed on one or +more expressions. Microsoft® SQL Server™ uses these operator categories:</p> +<ul> +<li>Arithmetic operators</li> +<li>Assignment operator</li> +<li>Bitwise operators</li> +<li>Comparison operators</li> +<li>Logical operators</li> +<li>String concatenation operator</li> +<li>Unary operators</li> +</ul> +<h5>Arithmetic Operators</h5> +<p>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 <a href= +"http://doc.ddart.net/mssql/sql70/syntaxc.htm"><font color= +"#0000FF">Transact-SQL Syntax Conventions</font></a>.</p> +<table cellspacing="0" cols="2" width="462"> +<tbody> +<tr valign="top"> +<th align="left" width="38%">Operator</th> +<th align="left" width="62%">Meaning</th> +</tr> +<tr valign="top"> +<td width="38%"><a href= +"http://doc.ddart.net/mssql/sql70/operator.htm"><font color="#0000FF">+ +(Add)</font></a></td> +<td width="62%">Addition.</td> +</tr> +<tr valign="top"> +<td width="38%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_4.htm"><font color="#0000FF">- +(Subtract)</font></a></td> +<td width="62%">Subtraction.</td> +</tr> +<tr valign="top"> +<td width="38%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_5.htm"><font color="#0000FF">* +(Multiply)</font></a></td> +<td width="62%">Multiplication.</td> +</tr> +<tr valign="top"> +<td width="38%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_6.htm"><font color="#0000FF">/ +(Divide)</font></a></td> +<td width="62%">Division.</td> +</tr> +<tr valign="top"> +<td width="38%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_7.htm"><font color="#0000FF">% +(Modulo)</font></a></td> +<td width="62%">Returns the integer remainder of a division. For example, 12 % +5 = 2 because the remainder of 12 divided by 5 is 2.</td> +</tr> +</tbody> +</table> +<p> </p> +<p>The + and - operators can also be used to perform arithmetic operations on +<strong>datetime</strong> and <strong>smalldatetime</strong> values.</p> +<p>For information about the precision and scale of the result of an arithmetic +operation, see <a href= +"http://doc.ddart.net/mssql/sql70/da-db_3.htm"><font color="#0000FF">Precision, +Scale, and Length</font></a>.</p> +<h5>Assignment Operator</h5> +<p>Transact-SQL has one assignment operator, the equals sign (=). In this +example, the <strong>@MyCounter</strong> variable is created. Then, the +assignment operator sets <strong>@MyCounter</strong> to a value returned by an +expression.</p> +<p class="ex"><code>DECLARE @MyCounter INT</code></p> +<p class="ex"><code>SET @MyCounter = 1</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<p>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 +<strong>FirstColumnHeading</strong> and <strong>SecondColumnHeading</strong>. +The string xyz is displayed in the <strong>FirstColumnHeading</strong> column +heading for all rows. Then, each product ID from the <strong>Products</strong> +table is listed in the <strong>SecondColumnHeading</strong> column heading.</p> +<p class="ex"><code>USE Northwind</code></p> +<p class="ex"><code>GO</code></p> +<p class="ex"><code>SELECT FirstColumnHeading = 'xyz',</code></p> +<p class="ex"><code> SecondColumnHeading = ProductID</code></p> +<p class="ex"><code>FROM Products</code></p> +<p class="ex"><code>GO</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<h5>Bitwise Operators</h5> +<p>Bitwise operators perform bit manipulations between two expressions of any +of the data types of the integer data type category.</p> +<table cellspacing="0" cols="2" width="460"> +<tbody> +<tr valign="top"> +<th align="left" width="39%">Operator</th> +<th align="left" width="61%">Meaning</th> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_9.htm"><font color="#0000FF">& +(Bitwise AND)</font></a></td> +<td width="61%">Bitwise AND (two operands).</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_10.htm"><font color="#0000FF">| +(Bitwise OR)</font></a></td> +<td width="61%">Bitwise OR (two operands).</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_11.htm"><font color="#0000FF">^ +(Bitwise Exclusive OR)</font></a></td> +<td width="61%">Bitwise exclusive OR (two operands).</td> +</tr> +</tbody> +</table> +<p> </p> +<p>The operands for bitwise operators can be any of the data types of the +integer or binary string data type categories (except for the +<strong>image</strong> 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.</p> +<table cellspacing="0" cols="2" width="446"> +<tbody> +<tr valign="top"> +<th align="left" width="40%">Left operand</th> +<th align="left" width="60%">Right operand</th> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/ba-bz_6.htm"><strong><font color= +"#0000FF">binary</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, or +<strong>tinyint</strong></td> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/ba-bz_7.htm"><strong><font color= +"#0000FF">bit</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, +<strong>tinyint</strong>, or <strong>bit</strong></td> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/ia-iz_29.htm"><strong><font color= +"#0000FF">int</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, +<strong>tinyint</strong>, <strong>binary</strong>, or +<strong>varbinary</strong></td> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/ia-iz_29.htm"><strong><font color= +"#0000FF">smallint</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, +<strong>tinyint</strong>, <strong>binary</strong>, or +<strong>varbinary</strong></td> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/ia-iz_29.htm"><strong><font color= +"#0000FF">tinyint</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, +<strong>tinyint</strong>, <strong>binary</strong>, or +<strong>varbinary</strong></td> +</tr> +<tr valign="top"> +<td width="40%"><a href= +"http://doc.ddart.net/mssql/sql70/va-vz_1.htm"><strong><font color= +"#0000FF">varbinary</font></strong></a></td> +<td width="60%"><strong>int</strong>, <strong>smallint</strong>, or +<strong>tinyint</strong></td> +</tr> +</tbody> +</table> +<p> </p> +<h5>Comparison Operators</h5> +<p>Comparison operators test whether or not two expressions are the same. +Comparison operators can be used on all expressions except expressions of the +<strong>text</strong>, <strong>ntext</strong>, or <strong>image</strong> data +types.</p> +<table cellspacing="0" cols="2" width="460"> +<tbody> +<tr valign="top"> +<th align="left" width="39%">Operator</th> +<th align="left" width="61%">Meaning</th> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_13.htm"><font color="#0000FF">= +(Equals)</font></a></td> +<td width="61%">Equal to.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_14.htm"><font color="#0000FF">> +(Greater Than)</font></a></td> +<td width="61%">Greater than.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_15.htm"><font color="#0000FF">< +(Less Than)</font></a></td> +<td width="61%">Less than.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_16.htm"><font color="#0000FF">>= +(Greater Than or Equal To)</font></a></td> +<td width="61%">Greater than or equal to.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_17.htm"><font color="#0000FF"><= +(Less Than or Equal To)</font></a></td> +<td width="61%">Less than or equal to.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_18.htm"><font color= +"#0000FF"><> (Not Equal To)</font></a></td> +<td width="61%">Not equal to.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_20.htm"><font color="#0000FF">!= +(Not Equal To)</font></a></td> +<td width="61%">Not equal to (not SQL-92 standard).</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_19.htm"><font color="#0000FF">!< +(Not Less Than)</font></a></td> +<td width="61%">Not less than (not SQL-92 standard).</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_21.htm"><font color="#0000FF">!> +(Not Greater Than)</font></a></td> +<td width="61%">Not greater than (not SQL-92 standard).</td> +</tr> +</tbody> +</table> +<p> </p> +<p>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.</p> +<p>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.</p> +<p>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.</p> +<p>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:</p> +<p class="ex"><code>USE Northwind</code></p> +<p class="ex"><code>GO</code></p> +<p class="ex"><code>DECLARE @MyProduct int</code></p> +<p class="ex"><code>SET @MyProduct = 10</code></p> +<p class="ex"><code>IF (@MyProduct <> 0)</code></p> +<p class="ex"><code> SELECT *</code></p> +<p class="ex"><code> FROM Products</code></p> +<p class="ex"><code> WHERE ProductID = @MyProduct</code></p> +<p class="ex"><code>GO</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<h5>Logical Operators</h5> +<p>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.</p> +<table cellspacing="0" cols="2" width="464"> +<tbody> +<tr valign="top"> +<th align="left" width="30%">Operator</th> +<th align="left" width="70%">Meaning</th> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/aa-az_2.htm"><font color= +"#0000FF">ALL</font></a></td> +<td width="70%">TRUE if all of a set of comparisons are TRUE.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/aa-az_8.htm"><font color= +"#0000FF">AND</font></a></td> +<td width="70%">TRUE if both Boolean expressions are TRUE.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/aa-az_9.htm"><font color= +"#0000FF">ANY</font></a></td> +<td width="70%">TRUE if any one of a set of comparisons are TRUE.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/ba-bz_5.htm"><font color= +"#0000FF">BETWEEN</font></a></td> +<td width="70%">TRUE if the operand is within a range.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/ea-ez_3.htm"><font color= +"#0000FF">EXISTS</font></a></td> +<td width="70%">TRUE if a subquery contains any rows.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/ia-iz_7.htm"><font color= +"#0000FF">IN</font></a></td> +<td width="70%">TRUE if the operand is equal to one of a list of +expressions.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/la-lz_2.htm"><font color= +"#0000FF">LIKE</font></a></td> +<td width="70%">TRUE if the operand matches a pattern.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/nos-nz.htm"><font color= +"#0000FF">NOT</font></a></td> +<td width="70%">Reverses the value of any other Boolean operator.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/oa-oz_7.htm"><font color= +"#0000FF">OR</font></a></td> +<td width="70%">TRUE if either Boolean expression is TRUE.</td> +</tr> +<tr valign="top"> +<td width="30%"><a href= +"http://doc.ddart.net/mssql/sql70/setu-sus_7.htm"><font color= +"#0000FF">SOME</font></a></td> +<td width="70%">TRUE if some of a set of comparisons are TRUE.</td> +</tr> +</tbody> +</table> +<p> </p> +<p>For more information about logical operators, see the specific logical +operator topic.</p> +<h5>String Concatenation Operator</h5> +<p>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.</p> +<p>By default, an empty string is interpreted as an empty string in INSERT or +assignment statements on data of the <strong>varchar</strong> data type. In +concatenating data of either the <strong>varchar</strong>, +<strong>char</strong>, or <strong>text</strong> data types, the empty string is +interpreted as an empty string. For example, ‘abc’ <em>+</em> ‘‘ <em>+</em> +‘def’ is stored as ‘abcdef’. However, if the <strong>sp_dbcmptlevel</strong> +compatibility level setting is 65, empty constants are treated as a single +blank character and ‘abc’ <em>+</em> ‘‘ <em>+</em> ‘def’ is stored as ‘abc +def’. For more information about the interpretation of empty strings, see +<a href="http://doc.ddart.net/mssql/sql70/sp_da-di_2.htm"><strong><font color= +"#0000FF">sp_dbcmptlevel</font></strong></a>.</p> +<h5>Unary Operators</h5> +<p>Unary operators perform an operation on only one expression of any of the +data types of the numeric data type category.</p> +<table cellspacing="0" cols="2" width="460"> +<tbody> +<tr valign="top"> +<th align="left" width="39%">Operator</th> +<th align="left" width="61%">Meaning</th> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_1.htm"><font color="#0000FF">+ +(Positive)</font></a></td> +<td width="61%">Numeric value is positive.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_3.htm"><font color="#0000FF">- +(Negative)</font></a></td> +<td width="61%">Numeric value is negative.</td> +</tr> +<tr valign="top"> +<td width="39%"><a href= +"http://doc.ddart.net/mssql/sql70/operator_12.htm"><font color="#0000FF">~ +(Bitwise NOT)</font></a></td> +<td width="61%">Returns the ones complement of the number.</td> +</tr> +</tbody> +</table> +<p> </p> +<p>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.</p> +<h5>Operator Precedence</h5> +<p>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.</p> +<p>Operators have these precedence levels. An operator on higher levels is +evaluated before an operator on a lower level:</p> +<ul> +<li>+ (Positive), - (Negative), ~ (Bitwise NOT)</li> +<li>* (Multiply), / (Division), % (Modulo)</li> +<li>+ (Add), (+ Concatenate), - (Subtract)</li> +<li>=, >, <, >=, <=, <>, !=, !>, !< (Comparison +operators)</li> +<li>^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)</li> +<li>NOT</li> +<li>AND</li> +<li>ALL, ANY, BETWEEN, IN, LIKE, OR, SOME</li> +<li>= (Assignment)</li> +</ul> +<p>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.</p> +<p class="ex"><code>DECLARE @MyNumber int</code></p> +<p class="ex"><code>SET @MyNumber = 4 - 2 + 27</code></p> +<p class="ex"><code>-- Evaluates to 2 + 27 which yields an expression result of +29.</code></p> +<p class="ex"><code>SELECT @MyNumber</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<p>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.</p> +<p>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.</p> +<p class="ex"><code>DECLARE @MyNumber int</code></p> +<p class="ex"><code>SET @MyNumber = 2 * 4 + 5</code></p> +<p class="ex"><code>-- Evaluates to 8 + 5 which yields an expression result of +13.</code></p> +<p class="ex"><code>SELECT @MyNumber</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<p>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.</p> +<p class="ex"><code>DECLARE @MyNumber int</code></p> +<p class="ex"><code>SET @MyNumber = 2 * (4 + 5)</code></p> +<p class="ex"><code>-- Evaluates to 2 * 9 which yields an expression result of +18.</code></p> +<p class="ex"><code>SELECT @MyNumber</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<p>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.</p> +<p class="ex"><code>DECLARE @MyNumber int</code></p> +<p class="ex"><code>SET @MyNumber = 2 * (4 + (5 - 3) )</code></p> +<p class="ex"><code>-- Evaluates to 2 * (4 + 2) which further evaluates to 2 * +6, and</code></p> +<p class="ex"><code>-- yields an expression result of 12.</code></p> +<p class="ex"><code>SELECT @MyNumber</code></p> +<p class="ex"><code><strong><em> </em></strong></code></p> +<h5>See Also</h5> +<p><a href="http://doc.ddart.net/mssql/sql70/fa-fz_15.htm"><font color= +"#0000FF">Functions</font></a></p>