Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Expressions in Adaptive Server

Arithmetic and character expressions

Arithmetic and character expressions

The general pattern for arithmetic and character expressions is:

{constant | column_name | function | (subquery)
     | (case_expression)}
         [{arithmetic_operator | bitwise_operator |
          string_operator | comparison_operator } 
     {constant | column_name | function | (subquery)
      | case_expression}]...

Operators have the following precedence levels, where 1 is the highest level and 6 is the lowest:

1. unary (single argument) - + ~

2. * /%

3. binary (two argument) + - & | ^

4. not

5. and

6. or

When all operators in an expression are of the same level, the order of execution is left to right. You can change the order of execution with parentheses--the most deeply nested expression is handled first.

Adaptive Server uses these arithmetic operators:

You can use addition, subtraction, division, and multiplication on exact numeric, approximate numeric, and money type columns.

You cannot use the modulo operator on smallmoney, money, float, or real columns. Modulo finds the integer remainder after a division involving two whole numbers. For example, 21% 11 = 10 because 21 divided by 11 equals 1 with a remainder of 10.

When you perform arithmetic operations on mixed datatypes (for example, float and int) Adaptive Server follows specific rules for determining the type of the result. See Chapter 6, "Using and Creating Datatypes," for more information.

The bitwise operators are a Transact-SQL extension for use with data of the type integer. These operators convert each integer operand into its binary representation and then evaluate the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.

Table 1-3 and Table 1-4 summarize the results for operands of 0 and 1. If either operand is NULL, the bitwise operator returns NULL:

The following examples use two tinyint arguments: A = 170 (10101010 in binary form) and B = 75 (01001011 in binary form).

The string operator + can concatenate two or more character or binary expressions. For example:

1. select Name = (au_lname + ",
   " + au_fname) 
   from authors

   Displays author names under the column heading "Name" in last-name, first-name order, with a comma after the last name; for example, "Bennett, Abraham."

2. select "abc" + "" + "def" 

   Returns the string "abc def". The empty string is interpreted as a single space in all char, varchar, nchar, nvarchar, and text concatenation, and in varchar insert and assignment statements.

When concatenating non-character, non-binary expressions, use convert:

select "The date is " + 
    convert(varchar(12), getdate()) 

Adaptive Server uses these comparison operators:

In comparing character data, < means closer to the beginning of the server's sort order and > means closer to the end of the sort order. Uppercase and lowercase letters are equal in a sort order that is not case sensitive. Use sp_helpsort to see the sort order for your Adaptive Server. Trailing blanks are ignored for comparison purposes.

In comparing dates, < means earlier than and > means later than.

Put single or double quotes around all character and datetime data used with a comparison operator:

= "Bennet" 
> "May 22 1947"

The following operators are Transact-SQL extensions:

• Modulo operator: %

• Negative comparison operators: !>, !<, !=

• Bitwise operators: ~, ^, |, &

• Join operators: *= and =*

Adaptive Server treats character constant expressions as varchar. If they are compared with non-varchar variables or column data, the datatype precedence rules are used in the comparison (that is, the datatype with lower precedence is converted to the datatype with higher precedence). If implicit datatype conversion is not supported, you must use the convert function (see "Supported conversions").

Comparison of a char expression to a varchar expression follows the datatype precedence rule; the "lower" datatype is converted to the "higher" datatype. All varchar expressions are converted to char (that is, trailing blanks are appended) for the comparison.

The empty string ("") or ('') is interpreted as a single blank in insert or assignment statements on varchar data. When varchar, char, nchar, or nvarchar data is concatenated, the empty string is interpreted as a single space. For example, this is stored as "abc def":

"abc" + "" + "def"

The empty string is never evaluated as NULL.

There are two ways to specify literal quotes within a char or varchar entry. The first method is to use an additional quote with a quote of the same type. This is called "escaping" the quote. For example, if you begin a character entry with a single quote, but you want to include a single quote as part of the entry, use two single quotes:

'I don't understand.' 

Here is an example containing internal double and single quotes. The single quote does not have to be escaped, but the double quote does:

"He said, ""It's not really confusing.""" 

The second method is to enclose a quote in the opposite kind of quotation mark. In other words, surround an entry containing a double quote with single quotes (or vice versa). Here are some examples:

'George said, "There must be a better way."' 
"Isn't there a better way?" 
'George asked, "Isn"t there a better way?"' 

To continue a character string that would go off the end of one line on your screen, enter a backslash (\) before going to the following line.

If the quoted_identifier option is set to on, do not use double quotes around character or date data. You must use single quotes, or Adaptive Server treats the data as an identifier. For more information about quoted identifiers, see "Delimited identifiers".