Operators for Expressions

See Also      Tasks

You can use a variety of operators in constructing expressions for your query, including mathematical and text operators.

Mathematical Operators

The following table lists the mathematical operators you can use in constructing an expression.

Note   Some databases make additional operators available, such as the "%" operator for modulus. For details about operators available in SQL Server, see Query Designer Considerations for SQL Server Databases. For other databases, refer to the documentation for your database.

If you use more than one mathematical operator in an expression, the Query Designer processes the expression according to the following operator precedence. To override the default precedence, use parentheses around the portion of the expression that is to be evaluated first. If more than one operator of the same level is included, the operators are evaluated left to right.

1. Unary + and -
   
   
2. * and /
   
   
3. + and -

Text Operator

You can perform one operation on text: concatenation or the linking together of strings. In some databases, you can concatenate strings and perform other operations (such as removing extraneous spaces) using a single operator.

To concatenate a string, you can use the "+" operator in the Grid pane. If your database supports a different concatenation operator, the Query Designer might convert the "+" operator to the appropriate operator in the SQL pane. If you are entering expressions directly into the SQL pane, use the concatenation operator supported by your database. For details, refer to the documentation for your database.

Note   Oracle uses the || operator.  For details, see Query Designer Considerations for Oracle Databases.

You can also use the ODBC CONCAT function, which is supported by ODBC-compliant drivers. For example, the following expression illustrates how you can use the CONCAT function to combine authors’ last name and first name with a comma between them:

{ fn CONCAT(au_lname,{ fn CONCAT (', ', au_fname)})}

For more details about functions you can use, see Functions for Expressions.