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.
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
ACOSH function Returns the hyperbolic tangent of the specified numeric input Trigonometric Functions ACOSH 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
ASINH function Returns the arc hyperbolic sine of the specified numeric input Trigonometric Functions ASINH Function
ATAN function Returns the arc tangent of numeric argument Trigonometric Functions ATAN Function
ATAN2 function Calculates the angular component of polar coordinate arguments Trigonometric Functions ATAN2 Function
ATANH function Returns the hyperbolic tangent of the specified numeric input Trigonometric Functions ATANH 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 Returns the number of elements in a collection data type (SET, MULTISET, or LIST) CARDINALITY Function CARDINALITY Function
CASE expression Returns a value that depends on which of several conditional tests evaluates to true CASE Expressions CASE Expressions
CAST expression Converts an expression to a specified data type CAST Expressions CAST Expressions
Cast ( :: ) operator See "Double-colon ( :: ) cast operator" CAST Expressions CAST Expressions
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 argument Length functions CHAR_LENGTH Function
CHARINDEX function Returns the location of a substring within a string CHARINDEX function CHARINDEX function
CHR Returns a code point in the range 0 through 255 from the default code set String-Manipulation Functions CHR Function
COALESCE function Returns the first non-NULL value from a series of expressions COALESCE Function COALESCE Function
Column expression 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
COSH function Returns the hyperbolic cosine of the argument, where the argument is an angle expressed in radians Trigonometric Functions COSH 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
CUME_DIST function Returns percentile rankings for each row in an OLAP partition OLAP ranking function expressions CUME_DIST 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
CURRENT_USER operator Returns the authorization identifier of the user. Synonym for USER operator. Constant Expressions USER or CURRENT_USER Operator
sequence.CURRVAL 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' and 'bigserial' options
DBINFO ('cdrsession') Shows whether a DML operation is part of a replicated transaction DBINFO Function Using the 'cdrsession' option
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', 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' and 'bigserial' options
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 'onedb' 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_ BINARY function Returns a plain-text BLOB data value after processing an encrypted BLOB argument Encryption and decryption functions DECRYPT_BINARY Function
DECRYPT_CHAR function Returns a plain-text string or CLOB after processing an encrypted argument Encryption and decryption functions DECRYPT_CHAR Function
DEFAULT_ROLE operator Returns the default role of the current user Constant Expressions DEFAULT_ROLE Operator
DEGREES function Converts units of radians to degrees Trigonometric Functions DEGREES function
DELETING Boolean operator Returns 't' if triggering event is a DELETE Trigger-Type Boolean Operator Trigger-Type Boolean Operator
DENSERANK function Synonym for the DENSE_RANK function OLAP ranking function expressions DENSE_RANK function
DENSE_RANK function Ranks each row in an OLAP partition, with no gaps in ranks OLAP ranking function expressions DENSE_RANK function
Division ( / ) operator Returns the quotient of two numeric operands Expression Arithmetic Operators
Double-colon ( :: ) cast operator Converts the value of an expression to a specified data type CAST Expressions CAST Expressions
Double-pipe ( || ) concatenation operator Returns a string that joins one string operand to another string operand Expression Concatenation Operator
ENCRYPT_AES function 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 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 Creates a BLOB value from data stored in a specified operating-system file Smart-Large-Object Functions FILETOBLOB and FILETOCLOB Functions
FILETOCLOB function Creates a CLOB value from data stored in a specified operating-system file Smart-Large-Object Functions FILETOBLOB and FILETOCLOB Functions
FIRST_VALUE function Returns the value of a specified expression for the first row in each OLAP window partition OLAP aggregation function expressions LAST_VALUE function
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 abbreviated units of memory or of storage FORMAT_UNITS Function FORMAT_UNITS Function
GETHINT function Returns a plain-text hint string after processing an encrypted data-string argument Encryption and decryption functions GETHINT Function
GREATEST function Returns the maximum value in a set of values Algebraic Functions GREATEST function
HEX function Returns the hexadecimal encoding of a base-10 integer argument HEX Function HEX Function
Host variable See Variable. Expression Expression
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
INSERTING Boolean operator Returns 't' if triggering event is an INSERT Trigger-Type Boolean Operator Trigger-Type Boolean Operator
INSTR function Returns position of Nth occurrence of a substring within a string INSTR function INSTR function
LAG function Returns an expression value for the row at a specified offset before the current row in an OLAP partition OLAP ranking function expressions LAG and LEAD functions
LAST_DAY function Returns the date of the last day of the month that its argument specifies Time Functions LAST_DAY Function
LAST_VALUE function Returns the value of a specified expression for the last row in an OLAP window partition OLAP aggregation function expressions LAST_VALUE function
LEAD function Returns an expression value for the row at a specified offset after the current row in an OLAP partition OLAP ranking function expressions LAG and LEAD functions
LEAST function Returns the minimum value in a set of values Algebraic Functions LEAST function
LEFT function Returns the leftmost N characters of a string LEFT function LEFT function
LEN function Synonym for the LENGTH function Length functions LENGTH Function
LENGTH function Returns the number of bytes in a character column, not including trailing blank spaces Length functions LENGTH Function
LIST collection constructor Constructor for ordered collections that can contain duplicate values Collection Constructors Collection Constructors
Literal BOOLEAN Literal representation of a BOOLEAN value Constant Expressions Constant Expressions
Literal collection Represents elements in a collection data type Constant Expressions Literal Collection
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 Represents an opaque data type Constant Expressions Constant Expressions
Literal row Represents the elements in a ROW data type Constant Expressions Literal Row
LN Returns the natural logarithm of a numeric argument Exponential and Logarithmic Functions LN function
LOCOPY function Creates a copy of a smart large object Smart-Large-Object Functions LOCOPY Function
LOG10 function Returns the base-10 logarithm of a numeric argument Exponential and Logarithmic Functions LOG10 Function
LOGN function Returns the natural logarithm of a numeric argument Exponential and Logarithmic Functions LOGN Function
LOTOFILE function Copies a BLOB or CLOB object to a file Smart-Large-Object Functions 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 arguments Time Functions MONTHS_BETWEEN Function
Multiplication ( * ) operator Returns the product of two numeric operands Expression Arithmetic Operators
MULTISET collection constructor 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 both of two conditions Time Functions NEXT_DAY Function
sequence.NEXTVAL Increments value of the specified sequence Constant Expressions Using NEXTVAL
NTILE function Classifies the rows in an OLAP partition into N ranked categories, called tiles, of similar cardinalities OLAP ranking function expressions NTILE function
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
NVL2 function Returns the second argument when the first argument is not NULL NVL2 Function NVL2 Function
OCTET_LENGTH function Returns the number of bytes in a character column, including any trailing blank spaces Length functions OCTET_LENGTH Function
PERCENT_RANK function Returns a ranking value for each row in an OLAP window partition, normalized to a range from 0 to 1 OLAP ranking function expressions PERCENT_RANK 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. Expression Expression
QUARTER function Returns the calendar quarter of a DATE or DATETIME value Time Functions QUARTER Function
Quoted string Literal character string Constant Expressions Quoted String
RADIANS function Converts units of degrees to radians Trigonometric Functions RADIANS function
RANGE function Returns the range of a specified set of values Aggregate Expressions RANGE Function
RANK Returns an ordinal number to rank each row in an OLAP window OLAP ranking function expressions RANK function
RATIOTOREPORT function Synonym for the RATIO_TO_REPORT function OLAP aggregation function expressions RATIO_TO_REPORT function
RATIO_TO_REPORT function Returns the fractional ratio of each row value to the sum for all rows in the same OLAP window partition OLAP aggregation function expressions RATIO_TO_REPORT function
REPLACE function Replaces specified characters in a source string String-Manipulation Functions REPLACE Function
REVERSE Reverses the order of characters in a source string String-Manipulation Functions REVERSE function
RIGHT function Returns the N rightmost characters from a source string RIGHT function RIGHT function
ROOT function Returns a real, positive, Nth 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 Constructor for a named ROW data type Constructor Expressions ROW constructors
ROWNUMBER function Synonym for the ROW_NUMBER function OLAP numbering function expression OLAP numbering function expression
ROW_NUMBER function Returns sequential integers for each row in an OLAP window partition OLAP numbering function expression OLAP numbering function expression
RPAD function Returns a string right-padded by a specified number of pad characters String-Manipulation Functions RPAD Function
RTRIM function Removes trailing blank pad characters from a string String-Manipulation Functions RTRIM Function
SECLABEL_BY_ COMP function Returns the security label whose components are the arguments Security Label Support Functions SECLABEL_BY_COMP Function
SECLABEL_BY_ NAME function Returns the security label whose identifier is the argument Security Label Support Functions SECLABEL_BY_NAME Function
SECLABEL_TO_ CHAR function Returns the security label whose string format is the argument Security Label Support Functions 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 Constructor for an unordered collection of unique elements Collection Constructors Collection Constructors
SIGN function Returns an indicator of the sign of the numeric argument SIGN function SIGN function
SIN function Returns the sine of a radians argument Trigonometric Functions SIN Function
SINH function Returns the hyperbolic sine of a radians argument Trigonometric Functions SINH function
SITENAME function See DBSERVERNAME function. Constant Expressions DBSERVERNAME and SITENAME Operators
SLV expression A statement-local variable (SLV) whose scope is the SQL statement that declares it Statement-Local Variable Declaration Statement-Local Variable Expressions
SPACE function Returns a string of N blank characters String-Manipulation Functions SPACE function
SPL routine expression See "User-defined functions" User-Defined Functions User-Defined Functions
SPL variable SPL variable that stores an expression Expression Expression
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 source string SUBSTR function SUBSTR function
SUBSTRB function Returns a substring of a source string SUBSTRB function SUBSTRB function
SUBSTRING function Returns a substring of a source string SUBSTRING function SUBSTRING function
SUBSTRING_INDEX function Returns a substring that includes the Nth occurrence of a delimiter SUBSTRING_INDEX function SUBSTRING_INDEX function
Substring ( [ x, y ] ) operator Returns a substring from 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
SYSDATE operator Returns the current DATETIME value from the system clock. Constant Expressions SYSDATE Operator
TAN function Returns the tangent of a radians expression Trigonometric Functions TAN Function
TANH function Returns the hyperbolic tangent of a radians argument Trigonometric Functions TANH Function
TO_CHAR function Converts a time or number to a character string Time Functions TO_CHAR Function
TO_DATE function Converts a character string to a DATETIME value Time Functions TO_DATE Function
TO_NUMBER function Converts a number or a character string to a DECIMAL value TO_NUMBER Function TO_NUMBER Function
TODAY operator Returns the current system date Constant Expressions TODAY Operator
TRIM function Drops blank pad characters from a character 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 Aggregate that a user defines (as opposed to a built-in aggregate) User-Defined Aggregates User-Defined Aggregates
User-defined function Function that a user writes (as opposed to a built-in function) User-Defined Functions User-Defined Functions
USER operator Returns the authorization identifier of the current user Constant Expressions USER or CURRENT_USER Operator
Variable Host or program variable that stores a value Expression Expression
VARIANCE function Returns the variance for a set of numeric values Aggregate Expressions VARIANCE Function
WEEKDAY function Returns an integer code for the day of the week Time Functions WEEKDAY Function
Window aggregate functions Return aggregate results from OLAP window partitions OLAP window expressions OLAP window aggregate functions
YEAR function Returns a 4-digit integer representing a year Time Functions YEAR Function
* symbol See "Multiplication ( * ) operator" Expression Arithmetic Operators
+ symbol See "Addition" and "Unary plus ( + ) sign" Expression Arithmetic Operators
- symbol See "Subtraction" and "Unary minus ( - ) sign" Expression Arithmetic Operators
/ symbol See "Division operator" Expression Arithmetic Operators
:: symbols See "Double-colon ( :: ) cast operator" CAST Expressions CAST Expressions
|| symbol See "Double-pipe ( || ) concatenation operator" Expression 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.