List of Expressions
Each category of SQL expression includes many individual expressions. The following table lists all the SQL expressions (and some operators) in alphabetical order. The columns in this table have the following meanings:
- Name gives the name of each expression.
- Description gives a short description of each expression.
- Syntax lists the page that shows the syntax of the expression.
- Usage shows the page that describes the usage of the expression.
Each expression listed in the following table is supported on all database servers unless otherwise noted. When an expression is not supported on all database servers, the Name column notes in parentheses the database server or servers that do support the expression.
| Name | Description | Syntax | Usage |
|---|---|---|---|
| ABS function | Returns absolute value of a numeric argument | Algebraic Functions | ABS Function |
| ACOS function | Returns the arc cosine of a numeric argument | Trigonometric Functions | ACOS Function |
| ADD_MONTHS function | Adds a specified number of months | Time Functions | ADD_MONTHS Function |
| Addition ( + ) operator | Returns the sum of two numeric operands | Expression | Arithmetic Operators |
| ASCII function | Returns the ASCII codepoint of the first character in its string argument | String-Manipulation Functions | ASCII Function |
| ASIN function | Returns the arc sine of a numeric argument | Trigonometric Functions | ASIN Function |
| ATAN function | Returns the arc tangent of numeric argument | Trigonometric Functions | ATAN Function |
| ATAN2 function | Computes the angular component of the polar coordinates (r, q) associated with (x, y) | Trigonometric Functions | ATAN2 Function |
| AVG function | Returns the mean of a set of numeric values | Aggregate Expressions | AVG Function |
| BITAND | Returns the bitwise AND of two arguments | Bitwise Logical Functions | BITAND Function |
| BITANDNOT | Returns the bitwise ANDNOT of two arguments | Bitwise Logical Functions | BITANDNOT Function |
| BITNOT | Returns the bitwise NOT of two arguments | Bitwise Logical Functions | BITNOT Function |
| BITOR | Returns the bitwise OR of two arguments | Bitwise Logical Functions | BITOR Function |
| BITXOR | Returns the bitwise XOR of two arguments | Bitwise Logical Functions | BITXOR Function |
| CARDINALITY function (IDS) | Returns the number of elements in a collection data type (SET, MULTISET, or LIST) | CARDINALITY Function (IDS) | CARDINALITY Function (IDS) |
| CASE expression | Returns a value that depends on which of several conditional tests evaluates to true | CASE Expressions | CASE Expressions |
| CAST expression (IDS) | Converts an expression to a specified data type | Cast Expressions (IDS) | Cast Expressions (IDS) |
| Cast ( :: ) operator | See "Double-colon ( :: ) cast operator" | Cast Expressions (IDS) | Cast Expressions (IDS) |
| CEIL function | Returns the smallest integer that is greater than or equal to its single argument | Algebraic Functions | CEIL Function |
| CHARACTER_ LENGTH function | See CHAR_LENGTH function. (In multibyte locales, this replaces the LENGTH function.) | Length Functions | CHAR_LENGTH Function |
| CHAR_LENGTH function | Returns count of logical characters in a string | Length Functions | CHAR_LENGTH Function |
| Column expression | Complete or partial column value from a table | Column Expressions | Column Expressions |
| CONCAT( ) operator function | Concatenates the results of two expressions | String-Manipulation Functions | CONCAT Function |
| Concatenation ( || ) operator | Concatenates the results of two expressions | Expression | Concatenation Operator |
| Constant expression | Expression with a literal, fixed, or variant value | Constant Expressions | Constant Expressions |
| COS function | Returns the cosine of a radian expression | Trigonometric Functions | COS Function |
| COUNT (as a set of functions) | Functions that return frequency counts Each form of the COUNT function is listed below. | Aggregate Expressions | Overview of COUNT Functions |
| COUNT (ALL column) function | See COUNT (column) function. | Aggregate Expressions | COUNT column Function |
| COUNT (column) function | Returns the number of non-NULL values in a specified column | Aggregate Expressions | COUNT column Function |
| COUNT DISTINCT function | Returns the number of unique non-NULL values in a specified column | Aggregate Expressions | COUNT DISTINCT and COUNT UNIQUE Functions |
| COUNT UNIQUE function | See COUNT DISTINCT function. | Aggregate Expressions | COUNT DISTINCT and COUNT UNIQUE Functions |
| COUNT (*) function | Returns the cardinality of the set of rows that satisfy a query | Aggregate Expressions | COUNT(*) Function |
| CURRENT operator | Returns the current time as a DATETIME value that consists of the date and the time of day | Constant Expressions | CURRENT Operator |
| CURRENT_ROLE operator | Returns the currently enabled role of the user | Constant Expressions | CURRENT_ROLE Operator |
| sequence.CURRVAL (IDS) | Returns the current value of specified sequence | Constant Expressions | Using CURRVAL |
| DATE function | Converts a nondate argument to a DATE value | Time Functions | DATE Function |
| DAY function | Returns the day of the month as an integer | Time Functions | DAY Function |
| DBINFO (option) | Functions for retrieving database and session information. Each option is listed below. | DBINFO Function | DBINFO Options |
| DBINFO ('bigserial') | Returns most recently inserted BIGSERIAL value | DBINFO Function | Using the 'serial8' Option and 'bigserial' Option |
| DBINFO ('serial8') | Returns most recently inserted SERIAL8 value | DBINFO Function | Using the 'serial8' Option and 'bigserial' Option |
| DBINFO ('coserverid', table. column, 'currentrow') (XPS) | Returns the coserver ID of the coserver where each row of a specified table is located | DBINFO Function | Using the 'coserverid' Option Followed by Table and Column Names (XPS) |
| DBINFO ('coserverid') (XPS) | Returns the coserver ID of the coserver to which the user who entered the query is connected | DBINFO Function | Using the 'coserverid' Option with No Other Arguments (XPS) |
| DBINFO ('dbhostname') | Returns the host name of the database server to which a client application is connected | DBINFO Function | Using the ‘dbhostname’ Option |
| DBINFO ('dbname') | Returns the identifier of the database to which a client application is connected | DBINFO Function | Using the ‘dbname' Option |
| DBINFO ('dbspace', table.column, 'currentrow') (XPS) | Returns the name of the dbspace where each row of a specified table is located | DBINFO Function | Using the 'dbspace' Option Followed by Table and Column Names (XPS) |
| DBINFO ('dbspace', tblspace_number) | Returns the name of a dbspace corresponding to a tblspace number | DBINFO Function | Using the ('dbspace', tblspace_num) Option |
| DBINFO ('get_tz' ) | Returns the time zone of the current session | DBINFO Function | Using the 'get_tz' Option |
| DBINFO ('serial8') | Returns most recently inserted SERIAL8 value | DBINFO Function | Using the 'serial8' Option and 'bigserial' Option |
| DBINFO ('sessionid') | Returns the session ID of the current session | DBINFO Function | Using the 'sessionid' Option |
| DBINFO ('sqlca.sqlerrd1') | Returns the last serial value inserted in a table | DBINFO Function | Using the 'sqlca.sqlerrd1' Option |
| DBINFO ('sqlca.sqlerrd2') | Returns the number of rows processed by DML statements, and by EXECUTE PROCEDURE and EXECUTE FUNCTION statements | DBINFO Function | Using the 'sqlca.sqlerrd2' Option |
| DBINFO ('utc_current') | Returns the current Coordinated Universal Time (UTC) value. | DBINFO Function | Using the 'utc_current' Option |
| DBINFO ('utc_to_datetime', expression) | Returns the DATETIME value of an integer or column expression that specifies a UTC value. | DBINFO Function | Using the 'utc_to_datetime' Option |
| DBINFO ('version', parameter) | Returns all or part, as specified by the parameter, of the exact version of the database server to which the client application is connected. | DBINFO Function | Using the ‘version' Option |
| DBSERVERNAME function | Returns the name of the database server | Constant Expressions | DBSERVERNAME and SITENAME Operators |
| DECODE function | Evaluates one or more expression pairs and compares the when expression in each pair with a specified value expression | DECODE Function | DECODE Function |
| DECRYPT_CHAR function (IDS) | Returns a plain-text string or CLOB after processing an encrypted argument | Encryption and Decryption Functions | DECRYPT_CHAR Function |
| DECRYPT_ BINARY function (IDS) | Returns a plain-text BLOB data value after processing an encrypted BLOB argument | Encryption and Decryption Functions | DECRYPT_BINARY Function |
| DEFAULT_ROLE operator | Returns the default role of the current user | Constant Expressions | DEFAULT_ROLE Operator |
| DELETING Boolean operator | Returns 't' if triggering event is a DELETE | Trigger-Type Boolean Operator | Trigger-Type Boolean Operator |
| Division ( / ) operator | Returns the quotient of two numeric operands | Expression | Arithmetic Operators |
| Double-colon ( :: ) cast operator (IDS) | Converts the value of an expression to a specified data type | Cast Expressions (IDS) | Cast Expressions (IDS) |
| Double-pipe ( || ) concatenation operator | Returns a string that joins one string operand to another string operand | Expression | Concatenation Operator |
| ENCRYPT_AES function (IDS) | Returns an encrypted string or BLOB after processing a plain-text string, BLOB, or CLOB | Encryption and Decryption Functions | ENCRYPT_AES Function |
| ENCRYPT_TDES function (IDS) | Returns an encrypted string or BLOB after processing a plain-text string, BLOB, or CLOB | Encryption and Decryption Functions | ENCRYPT_TDES Function |
| EXP function | Returns the exponent of a numeric expression | Exponential and Logarithmic Functions | EXP Function |
| EXTEND function | Resets precision of DATETIME or DATE value | Time Functions | EXTEND Function |
| FILETOBLOB function (IDS) | Creates a BLOB value from data stored in a specified operating-system file | Smart-Large-Object Functions (IDS) | FILETOBLOB and FILETOCLOB Functions |
| FILETOCLOB function (IDS) | Creates a CLOB value from data stored in a specified operating-system file | Smart-Large-Object Functions (IDS) | FILETOBLOB and FILETOCLOB Functions |
| FLOOR function | Returns the largest integer that is smaller than or equal to its single argument | Algebraic Functions | FLOOR Function |
| FORMAT_UNITS function | Returns a character string that specifies a number and the names of abbreviated units of memory or of storage | FORMAT_UNITS Function | FORMAT_UNITS Function |
| GETHINT function (IDS) | Returns a plain-text hint string after processing an encrypted data-string argument | Encryption and Decryption Functions | GETHINT Function |
| HEX function | Returns the hexadecimal encoding of a base-10 integer argument | HEX Function | HEX Function |
| Host variable | See Variable. | Syntax of SQL Expressions | Syntax of SQL Expressions |
| INSERTING Boolean operator | Returns 't' if triggering event is an INSERT | Trigger-Type Boolean Operator | Trigger-Type Boolean Operator |
| IFX_ALLOW_ NEWLINE function | Sets a newline session mode that allows or disallows newline characters in quoted strings | IFX_ALLOW_NEWLINE Function | IFX_ALLOW_NEWLINE Function |
| INITCAP function | Converts a string argument to a string in which only the initial letter of each word is uppercase | Case-Conversion Functions | INITCAP Function |
| LAST_DAY function | Returns the date of the last day of the month that its argument specifies | Time Functions | LAST_DAY Function |
| LENGTH function | Returns the number of bytes in a character column, not including any trailing blank spaces | Length Functions | LENGTH Function |
| LIST collection constructor (IDS) | Constructor for collections whose elements are ordered and can contain duplicate values | Collection Constructors | Collection Constructors |
| Literal BOOLEAN | Literal representation of a BOOLEAN value | Constant Expressions | Constant Expressions |
| Literal collection (IDS) | Represents elements in a collection data type | Constant Expressions | Literal Collection (IDS) |
| Literal DATETIME | Represents a DATETIME value | Constant Expressions | Literal DATETIME |
| Literal INTERVAL | Represents an INTERVAL value | Constant Expressions | Literal INTERVAL |
| Literal number | Represents a numeric value | Constant Expressions | Literal Number |
| Literal opaque type (IDS) | Represents an opaque data type | Constant Expressions | Constant Expressions |
| Literal row (IDS) | Represents the elements in a ROW data type | Constant Expressions | Literal Row (IDS) |
| LOCOPY function (IDS) | Creates a copy of a smart large object | Smart-Large-Object Functions (IDS) | LOCOPY Function |
| LOGN function | Returns the natural log of a numeric argument | Exponential and Logarithmic Functions | LOGN Function |
| LOG10 function | Returns the base-10 logarithm of an argument | Exponential and Logarithmic Functions | LOG10 Function |
| LOTOFILE function (IDS) | Copies a BLOB or CLOB value to a file | Smart-Large-Object Functions (IDS) | LOTOFILE Function |
| LOWER function | Converts uppercase letters to lowercase | Case-Conversion Functions | LOWER Function |
| LPAD function | Returns a string that is left-padded by a specified number of pad characters | String-Manipulation Functions | LPAD Function |
| LTRIM function | Removes specified leading pad characters from a string. | String-Manipulation Functions | LTRIM Function |
| MAX function | Returns the largest in a specified set of values | Aggregate Expressions | MAX Function |
| MDY function | Returns a DATE value from integer arguments | Time Functions | MDY Function |
| MIN function | Returns the smallest in a specified set of values | Aggregate Expressions | MIN Function |
| MOD function | Returns the modulus (the integer-division remainder value) from two numeric arguments | Algebraic Functions | MOD Function |
| MONTH function | Returns the month value from a DATE or DATETIME argument | Time Functions | MONTH Function |
| MONTHS_ BETWEEN function | Returns the difference in months between two time values | Time Functions | MONTHS_BETWEEN Function |
| Multiplication ( * ) operator | Returns the product of two numeric operands | Expression | Arithmetic Operators |
| MULTISET collection constructor (IDS) | Constructor for a non-ordered collection of elements that can contain duplicate value | Collection Constructors | Collection Constructors |
| NEXT_DAY function | Returns the earliest calendar date that satisfies each of two conditions | Time Functions | NEXT_DAY Function |
| sequence.NEXTVAL (IDS) | Increments value of the specified sequence | Constant Expressions | Using NEXTVAL |
| NULL keyword | Unknown, missing, or logically undefined value | NULL Keyword | NULL Keyword |
| NULLIF function | Returns NULL if both arguments are equal | NULLIF Function | NULLIF Function |
| NVL function | Returns the value of a not-NULL argument, or a specified value if the argument is NULL | NVL Function | NVL Function |
| OCTET_LENGTH function | Returns the number of bytes in a character column, including any trailing blank spaces | Length Functions | OCTET_LENGTH Function |
| POW function | Raises a base value to a specified power | Algebraic Functions | POW Function |
| POWER function | Synonym for POW function | Algebraic Functions | POW Function |
| Procedure-call expression | See user-defined function. | User-Defined Functions | User-Defined Functions |
| Program variable | See variable. | Syntax of SQL Expressions | Syntax of SQL Expressions |
| Quoted string | Literal character string | Constant Expressions | Quoted String |
| RANGE function | Returns the range of a specified set of values | Aggregate Expressions | RANGE Function |
| REPLACE function | Replaces specified characters in a source string | String-Manipulation Functions | REPLACE Function |
| ROOT function | Returns the root value of a numeric argument | Algebraic Functions | ROOT Function |
| ROUND function | Returns the rounded value of an argument | Algebraic Functions | ROUND Function |
| ROW constructor (IDS) | Constructor for a named ROW data type | Constructor Expressions (IDS) | ROW Constructors |
| RPAD function | Returns a string that is right-padded by a specified number of pad characters | String-Manipulation Functions | RPAD Function |
| RTRIM function | Removes trailing pad characters from a string | String-Manipulation Functions | RTRIM Function |
| SECLABEL_BY_ COMP function | Returns the individual components of a row security label for INSERT and UPDATE operations | Security Label Support Functions (IDS) | SECLABEL_BY_NAME Function |
| SECLABEL_BY_ NAME function | Returns the identifier of a row security label for INSERT and UPDATE operations | Security Label Support Functions (IDS) | SECLABEL_BY_COMP Function |
| SECLABEL_TO_ CHAR function | Returns the details of the individual components of a row security label for SELECT operations | Security Label Support Functions (IDS) | SECLABEL_TO_CHAR Function |
| SELECTING Boolean operator | Returns 't' if triggering event is a SELECT | Trigger-Type Boolean Operator | Trigger-Type Boolean Operator |
| SET collection constructor (IDS) | Constructor for an unordered collection of elements in which each value is unique | Collection Constructors | Collection Constructors |
| SIN function | Returns the sine of a radian expression | Trigonometric Functions | SIN Function |
| SITENAME function | See DBSERVERNAME function. | Constant Expressions | DBSERVERNAME and SITENAME Operators |
| SPL routine expression | See "User-defined functions" | User-Defined Functions | User-Defined Functions |
| SPL variable | SPL variable that stores an expression | Syntax of SQL Expressions | Syntax of SQL Expressions |
| SQLCODE function | Returns sqlca.sqlcode value to an SPL UDR | SQLCODE Function (SPL) | SQLCODE Function (SPL) |
| SQRT function | Returns the square root of a numeric argument | Algebraic Functions | SQRT Function |
| STDEV function | Returns the standard deviation of a data set | Aggregate Expressions | STDEV Function |
| SUBSTR function | Returns a substring of a string argument | SUBSTR Function | SUBSTR Function |
| SUBSTRING function | Returns a substring of a source string | SUBSTRING Function | SUBSTRING Function |
| Substring ( [ x, y ] ) operator | Returns a substring of a string operand | Column Expressions | Using the Substring Operator |
| Subtraction ( - ) operator | Returns the difference between two numbers | Expression | Arithmetic Operators |
| SUM function | Returns the sum of a specified set of values | Aggregate Expressions | SUM Function |
| SLV expression | A statement-local variable (SLV) whose scope is the SQL statement that declares it | Statement-Local Variable Declaration (IDS) | Statement-Local Variable Expressions (IDS) |
| SYSDATE operator | Returns the current DATETIME value from the system clock. | Constant Expressions | SYSDATE Operator |
| TAN function | Returns the tangent of a radian expression | Trigonometric Functions | TAN Function |
| TO_CHAR function | Converts a time or number to a string | Time Functions | TO_CHAR Function (IDS) |
| TO_DATE function | Converts a string to a DATETIME value | Time Functions | TO_DATE Function (IDS) |
| TO_NUMBER function | Converts two numbers or two strings to DECIMAL | TO_NUMBER Function | TO_NUMBER Function |
| TODAY operator | Returns the current system date | Constant Expressions | TODAY Operator |
| TRIM function | Drops pad characters from a string argument | String-Manipulation Functions | TRIM Function |
| TRUNC function | Returns a truncated numeric or time value | Algebraic Functions | TRUNC Function |
| Unary minus ( - ) sign | Specifies a negative ( < 0 ) numeric value | Expression | Arithmetic Operators |
| Unary plus ( + ) sign | Specifies a positive ( > 0 ) numeric value . | Expression | Arithmetic Operators |
| UNITS operator | Convert an integer to an INTERVAL value | Constant Expressions | UNITS Operator |
| UPDATING Boolean operator | Returns 't' if triggering event is an UPDATE | Trigger-Type Boolean Operator | Trigger-Type Boolean Operator |
| UPPER function | Converts lowercase letters to uppercase | Case-Conversion Functions | UPPER Function |
| User-defined aggregate (IDS) | Aggregate that you define (as opposed to built-in aggregates that Dynamic Server provides) | User-Defined Aggregates (IDS) | User-Defined Aggregates (IDS) |
| User-defined function | Function that you write (as opposed to built-in functions that the database server provides) | User-Defined Functions | User-Defined Functions |
| USER operator | Returns the login name of the current user | Constant Expressions | USER Operator |
| Variable | Host or program variable that stores a value | Syntax of SQL Expressions | Syntax of SQL Expressions |
| VARIANCE function | Returns the variance for a set of values | Aggregate Expressions | VARIANCE Function |
| WEEKDAY function | Returns an integer code for the day of the week | Time Functions | WEEKDAY Function |
| YEAR function | Returns a 4-digit integer representing a year | Time Functions | YEAR Function |
| * symbol | See "Multiplication ( * ) operator" | Syntax of SQL Expressions | Arithmetic Operators |
| + symbol | See "Addition" and "Unary plus ( + ) sign" | Syntax of SQL Expressions | Arithmetic Operators |
| - symbol | See "Subtraction" and "Unary minus ( - ) sign" | Syntax of SQL Expressions | Arithmetic Operators |
| / symbol | See "Division operator" | Syntax of SQL Expressions | Arithmetic Operators |
| :: symbols | See "Double-colon ( :: ) cast operator" | Cast Expressions (IDS) | Cast Expressions (IDS) |
| || symbol | See "Double-pipe ( || ) concatenation operator" | Syntax of SQL Expressions | Concatenation Operator |
| [ first, last ] symbols | See "Substring operator" | Column Expressions | Using the Substring Operator |
Sections that follow describe the syntax and usage of each expression that appears in the preceding table.