All Products
Search
Document Center

Overview of SQL expressions

Last Updated: Jun 18, 2021

Expressions are used to calculate data values. An expression is a combination of one or more components, such as numeric values, operators, and SQL functions. In general, an expression assumes the data types of its components.

Notice

The values of the NLS_COMP and NLS_SORT parameters jointly affect the sorting and comparison of characters. If the NLS_COMP parameter in the database is set to LINGUISTIC, all the entities described in this chapter follow the rules that are specified by the NLS_SORT parameter. If the NLS_COMP parameter is not set to LINGUISTIC, the functions are not affected by NLS_SORT. You can directly specify the value of NLS_SORT. If you do not specify it, it inherits the value of NLS_LANGUAGE.

The result of the following simple expression is 4 and of the NUMBER data type. This data type is consistent with the data type of the components.

2*2

The following complex expression uses functions and operators. This expression adds seven days to the current date, removes the time portion, and then converts the result to the CHAR data type.

TO_CHAR(TRUNC(SYSDATE+7))

You can also use expressions in the following scenarios:

  • The selected columns in SELECT statements
  • The WHERE and HAVING clauses

  • The CONNECT BY, START WITH, and ORDER BY clauses

  • The VALUES clauses of INSERT statements
  • The SET clauses of UPDATE statements

For example, you can use an expression to replace the string Smith in the SET clause of the following UPDATE statement:

SET last_name = 'Smith';

In the SET clause, the expression INITCAP(last_name) is used to replace the string Smith.

SET last_name = INITCAP(last_name);

In ApsaraDB for OceanBase, not all expressions can be directly used by SQL statements. For more information, see the limits on expressions in the SQL statements topic.