Common Functions
- abs
Returns the absolute value of "numeric_expression". Negative values are returned as positive values. - cast
Converts "expression" to a specified data type. Some data types allow for a length and precision to be specified. Make sure that the target is of the appropriate type and size. The following can be used for "datatype_specification": character, varchar, char, numeric, decimal, integer, bigint, smallint, real, float, date, time, timestamp, time with time zone, timestamp with time zone, and interval. When type casting to an interval type, one of the following interval qualifiers must be specified: year, month, or year to month for the year-to-month interval datatype; day, hour, minute, second, day to hour, day to minute, day to second, hour to minute, hour to second, or minute to second for the day-to-second interval datatype. Notes: When you convert a value of type timestamp to type date, the time portion of the timestamp value is ignored. When you convert a value of type timestamp to type time, the date portion of the timestamp is ignored. When you convert a value of type date to type timestamp, the time components of the timestamp are set to zero. When you convert a value of type time to type timestamp, the date component is set to the current system date. It is invalid to convert one interval datatype to the other (for instance because the number of days in a month is variable). Note that you can specify the number of digits for the leading qualifier only, i.e. YEAR(4) TO MONTH, DAY(5). Errors will be reported if the target type and size are not compatible with the source type and size. - ceil
Returns the smallest integer that is greater than or equal to "numeric_expression". - ceiling
Returns the smallest integer that is greater than or equal to "numeric_expression". - char_length
Returns the number of logical characters in "string_expression". The number of logical characters can be distinct from the number of bytes in some East Asian locales. - character_length
Returns the number of characters in "string_expression". - coalesce
Returns the first non-null argument (or null if all arguments are null). Requires two or more arguments in "expression_list". - current_date
Returns a date value representing the current date of the computer that the database software runs on. - current_time
Returns a time with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM® Cognos® BI software. - current_timestamp
Returns a datetime with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the computer that runs IBM® Cognos® BI software. - exp
Returns 'e' raised to the power of "numeric_expression". The constant 'e' is the base of the natural logarithm. - extract
Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in "datetime_expression". - floor
Returns the largest integer that is less than or equal to "numeric_expression". - ln
Returns the natural logarithm of "numeric_expression". - localtime
Returns a time value, representing the current time of the computer that runs the database software. - localtimestamp
Returns a datetime value, representing the current timestamp of the computer that runs the database software. - lower
Returns "string_expression" with all uppercase characters shifted to lowercase. - mod
Returns the remainder (modulus) of "integer_expression1" divided by "integer_expression2". "Integer_expression2" must not be zero or an exception condition is raised. - nullif
Returns null if "expression1" equals "expression2", otherwise returns "expression1". - octet_length
Returns the number of bytes in "string_expression". - position
Returns the integer value representing the starting position of "string_expression1" in "string_expression2" or 0 when the "string_expression1" is not found. - power
Returns "numeric_expression1" raised to the power "numeric_expression2". If "numeric_expression1" is negative, then "numeric_expression2" must result in an integer value. - _round
Returns "numeric_expression" rounded to "integer_expression" places to the right of the decimal point. Notes: "integer_expression" must be a non-negative integer. Rounding takes place before data formatting is applied. - row
The row constructor represents a collection of values organized as a row of data. It can be used in conditional expressions (i.e. IF-THEN-ELSE) and filter expressions (i.e. IN clause). - sqrt
Returns the square root of "numeric_expression". "Numeric_expression" must be non-negative. - substring
Returns the substring of "string_expression" that starts at position "integer_expression1" for "integer_expression2" characters or to the end of "string_expression" if "integer_expression2" is omitted. The first character in "string_expression" is at position 1. - trim
Returns "string_expression" trimmed of leading and trailing blanks or trimmed of a certain character specified in "match_character_expression". "Both" is implicit when the first argument is not stated and blank is implicit when the second argument is not stated. - upper
Returns "string_expression" with all lowercase characters converted to uppercase. - width-bucket
For a given expression, this function returns the bucket number into which the value of this expression would fall after being evaluated. - Trigonometric functions
Parent topic: Using the expression editor