Home

	Expand Search

Reference Manual Volumes 1 - 4 (Online Only)

Chapter 4 Expressions, Identifiers, and Wildcard Characters

Expressions

Expressions

An expression is a combination of one or more constants, literals, functions, column identifiers and/or variables, separated by operators, that returns a single value. Expressions can be of several types, including arithmetic, relational, logical (or Boolean), and character string. In some Transact-SQL clauses, a subquery can be used in an expression. A case expression can be used in an expression.

Table 4-1 lists the types of expressions that are used in Adaptive Server syntax statements.

Size of expressions

Expressions returning binary or character datum can be up to 16384 bytes in length. However, earlier versions of Adaptive Server only allowed expressions to be up to 255 bytes in length. If you have upgraded from an earlier release of Adaptive Server, and your stored procedures or scripts store a result string of up to 255 bytes, the remainder will be truncated. You may have to re-write these stored procedures and scripts for to account for the additional length of the 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}]...

Relational and logical expressions

A logical expression or relational expression returns TRUE, FALSE, or UNKNOWN. The general patterns are:

expression comparison_operator [any | all] expression

expression [not] in expression 

[not]exists expression 

expression [not] between expression and expression

expression [not] like "match_string"
[escape "escape_character "]

not expression like "match_string" 
[escape "escape_character "]

expression is [not] null

not logical_expression

logical_expression {and | or} logical_expression

Operator precedence

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 at 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 processed first.

Arithmetic operators

Adaptive Server uses the following arithmetic operators:

Addition, subtraction, division, and multiplication can be used on exact numeric, approximate numeric, and money type columns.

The modulo operator cannot be used 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. For more information, see Chapter 1, "System and User-Defined Datatypes."

Bitwise operators

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

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

The examples in Table 4-4 use two tinyint arguments, A = 170 (10101010 in binary form) and B = 75 (01001011 in binary form).

String concatenation operator

The string operator + can be used to concatenate two or more character or binary expressions. For example, the following displays author names under the column heading Namein last-name first-name order, with a comma after the last name; for example, "Bennett, Abraham.":

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

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

select "abc" + "" + "def"

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

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

A string concatenated with NULL evaluates to the value of the string. This is an exception to the SQL standard, which states that a string concatenated with a NULL should evaluate to NULL.

Comparison operators

Adaptive Server uses the comparison operators listed in Table 4-5:

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 case-insensitive sort order. Use sp_helpsort to see the sort order for your Adaptive Server. Trailing blanks are ignored for comparison purposes. So, for example, "Dirk" is the same as "Dirk ".

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

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

= "Bennet" 
> "May 22 1947"

Nonstandard operators

The following operators are Transact-SQL extensions:

• Modulo operator: %

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

• Bitwise operators: ~, ^, |, &

• Join operators: *= and =*

Using any, all and in

any is used with <, >, or = and a subquery. It returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement. For more information, see the Transact-SQL User's Guide.

all is used with < or > and a subquery. It returns results when all values retrieved in the subquery are less than (<) or greater than (>) the value in the where or having clause of the outer statement. For more information, see the Transact-SQL User's Guide.

in returns results when any value returned by the second expression matches the value in the first expression. The second expression must be a subquery or a list of values enclosed in parentheses. in is equivalent to = any.For more information, see where clause.

Negating and testing

not negates the meaning of a keyword or logical expression.

Use exists, followed by a subquery, to test for the existence of a particular result.

Ranges

between is the range-start keyword; and is the range-end keyword. The following range is inclusive:

 where column1 between x and y

The following range is not inclusive:

 where column1 > x and column1 < y

Using nulls in expressions

Use is null or is not null in queries on columns defined to allow null values.

An expression with a bitwise or arithmetic operator evaluates to NULL if any of the operands are null. For example, the following evaluates to NULL if column1 is NULL:

1 + column1

In general, the result of comparing null values is UNKNOWN, since it is not possible to determine whether NULL is equal (or not equal) to a given value or to another NULL. However, the following cases return TRUE when expression is any column, variable or literal, or combination of these, which evaluates as NULL:

• expression is null

• expression = null

• expression = @x, where @x is a variable or parameter containing NULL. This exception facilitates writing stored procedures with null default parameters.

• expression != n, where n is a literal that does not contain NULL, and expression evaluates to NULL.

The negative versions of these expressions return TRUE when the expression does not evaluate to NULL:

• expression is not null

• expression != null

• expression != @x

The far right side of these exceptions is a literal null, or a variable or parameter containing NULL. If the far right side of the comparison is an expression (such as @nullvar + 1), the entire expression evaluates to NULL.

Following these rules, null column values do not join with other null column values. Comparing null column values to other null column values in a where clause always returns UNKNOWN for null values, regardless of the comparison operator, and the rows are not included in the results. For example, this query returns no result rows where column1 contains NULL in both tables (although it may return other rows):

select column1 
from table1, table2 
where table1.column1 = table2.column1

Although neither FALSE nor UNKNOWN returns values, there is an important logical difference between FALSE and UNKNOWN, because the opposite of false ("not false") is true. For example, "1 = 2" evaluates to false and its opposite, "1 != 2", evaluates to true. But "not unknown" is still unknown. If null values are included in a comparison, you cannot negate the expression to get the opposite set of rows or the opposite truth value.

Only columns for which NULL was specified in the create table statement and into which you have explicitly entered NULL (no quotes), or into which no data has been entered, contain null values. Avoid entering the character string "NULL" (with quotes) as data for a character column. It can only lead to confusion. Use "N/A", "none", or a similar value instead. When you want to enter the value NULL explicitly, do not use single or double quotes.

The empty string (" "or ' ') is always stored as a single space in variables and column data. This concatenation statement is equivalent to "abc def", not to "abcdef":

"abc" + "" + "def"

The empty string is never evaluated as NULL.

Connecting expressions

and connects two expressions and returns results when both are true. or connects two or more conditions and returns results when either of the conditions is true.

When more than one logical operator is used in a statement, and is evaluated before or. You can change the order of execution with parentheses.

Table 4-6 shows the results of logical operations, including those that involve null values:

The result UNKNOWN indicates that one or more of the expressions evaluates to NULL, and that the result of the operation cannot be determined to be either TRUE or FALSE. See "Using nulls in expressions" for more information.

Using parentheses in expressions

Parentheses can be used to group the elements in an expression. When "expression" is given as a variable in a syntax statement, a simple expression is assumed. "Logical expression" is specified when only a logical expression is acceptable.

Comparing character expressions

Character constant expressions are treated 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.

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. If a unichar expression is compared to a char (varchar, nchar, nvarchar) expression, the latter is implicitly converted to unichar.

Using the empty string

The empty string (

""

''

"abc" + "" + "def"

The empty string is never evaluated as NULL.

Including quotation marks in character expressions

There are two ways to specify literal quotes within a char, or varchar entry. The first method is to double the quotes. For example, if you begin a character entry with a single quote and you want to include a single quote as part of the entry, use two single quotes:

'I don''t understand.'

With double quotes:

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

The second method is to enclose a quote in the opposite kind of quote 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?"' 

Using the continuation character

To continue a character string to the next line on your screen, enter a backslash (

\