DB2

DB2 Math
DB2 Trigonometry
ascii
Returns the ASCII code value of the leftmost character of the argument as an integer.
ceiling
Returns the smallest integer greater than or equal to "numeric_expression".
char
Returns a string representation of a date/time value or a decimal number.
chr
Returns the character that has the ASCII code value specified by "integer_expression". "Integer_expression" should be between 0 and 255.
concat
Returns a string that is the result of concatenating "string_expression1" with "string_expression2".
date
Returns a date from a single input value. "Expression" can be a string or integer representation of a date.
day
Returns the day of the month (1-31) from "date_expression". "Date_expression" can be a date value or a string representation of a date.
dayname
Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of "date_expression". "Date_expression" can be a date value or a string representation of a date.
dayofweek
Returns the day of the week in "date_expression" as an integer in the range 1 to 7, where 1 represents Sunday. "date_expression" can be a date value or a string representation of a date.
dayofweek_iso
Returns the day of the week in "date_expression" as an integer in the range 1 to 7, where 1 represents Monday. "date_expression" can be a date value or a string representation of a date.
dayofyear
Returns the day of the year in "date_expression" as an integer in the range 1 to 366. "Date_expression" can be a date value or a string representation of a date.
days
Returns an integer representation of a date. "Expression" can be a date value or a string representation of a date.
dec
Returns the decimal representation of "string-expression" using "precision-integer" and "scale-integer". The "decimal-character" can be used to specify the single-byte character constant used to delimit the decimal digits in "string-expression". The "string-expression" must be formatted as an SQL Integer or Decimal constant. Alternate syntax: dec ( numeric-expression [ , precision-integer [ , scale-integer ] ] ) Returns the decimal representation of "numeric-expression" using "precision-integer" and "scale-integer".
decimal
Returns the decimal representation of "string-expression" using "precision-integer" and "scale-integer". The "decimal-character" can be used to specify the single-byte character constant used to delimit the decimal digits in "string-expression". The "string-expression" must be formatted as an SQL Integer or Decimal constant. Alternate syntax: decimal ( numeric-expression [ , precision-integer [ , scale-integer ] ] ) Returns the decimal representation of "numeric-expression" using "precision-integer" and "scale-integer".
difference
Returns an integer value representing the difference between the values returned by the data source-specific soundex function for "string_expression1" and "string_expression2". The value returned ranges from 0 to 4, with 4 indicating the best match. Note that 4 does not mean that the strings are equal.
digits
Returns the character string representation of a non-floating point number.
double
Returns the floating-point representation of an expression. "Expression" can either be a numeric or string expression.
event_mon_state
Returns the operational state of a particular state monitor.
float
Returns the floating-point representation of a number.
hex
Returns the hexadecimal representation of a value.
hour
Returns the hour, an integer from 0 (midnight) to 23 (11:00 pm), from "time_expression". "Time_expression" can be a time value or a string representation of a time.
insert
Returns a string where "integer_expression2" characters have been deleted from "string_expression1" beginning at "integer_expression1" and where "string_expression2" has been inserted into "string_expression1" at its start. The first character in the string is at position 1.
integer
Returns the integer representation of an expression. "Expression" can be a numeric value or a string representation of a number.
int
Returns the integer representation of an expression. "Expression" can be a numeric value or a string representation of a number.
julian_day
Returns an integer value representing the number of days from January 1, 4712 BC (the start of the Julian date calendar) to the date value specified in "expression". "Expression" can be a date value or a string representation of a date.
lcase
Returns "string_expression" with all uppercase characters shifted to lowercase.
left
Returns the leftmost "integer_expression" characters of "string_expression".
length
Returns the length of the operand in bytes. Exception: double byte string types return the length in characters.
locate
Returns the starting position of the first occurrence of "string_expression1" within "string_expression2". The search starts at position start "integer_expression" of "string_expression2". The first character in a string is at position 1. If "string_expression1" is not found, zero is returned.
long_varchar
Returns a long string.
ltrim
Returns "string_expression" with leading spaces removed.
microsecond
Returns the microsecond (time-unit) part of a value. "Expression" can be a timestamp or a string representation of a timestamp.
midnight_seconds
Returns an integer value in the range 0 to 86400 representing the number of seconds between midnight and time value specified in the argument. "Expression" can be a time value, a timestamp or a string representation of a time.
minute
Returns the minute (an integer from 0-59) from "time_expression". "Time_expression" can be a time value, a timestamp, or a string representation of a time.
month
Returns the month (an integer from 1-12) from "date_expression".
monthname
Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for an English data source, or Januar through Dezember for a German data source) for the month portion of "date_expression".
quarter
Returns the quarter in "date_expression" as a number in the range 1 to 4, where 1 represents January 1 through March 31.
radians
Returns the number of radians converted from "numeric_expression" degrees.
repeat
Returns a string consisting of "string_expression" repeated "integer_expression" times.
replace
Replaces all occurrences of "string_expression2" in "string_expression1" with "string_expression3".
right
Returns the rightmost "integer_expression" characters of "string_expression".
round
Returns "numeric_expression" rounded to "integer_expression" places to the right of the decimal point. If "integer_expression" is negative, "numeric_expression" is rounded to the nearest absolute value "integer_expression" places to the left of the decimal point. Rounding takes place before data formatting is applied.
rtrim
Returns "string_expression" with trailing spaces removed.
second
Returns the second (an integer from 0-59) from "time_expression".
sign
Returns an indicator of the sign of "numeric_expression": +1 if "numeric_expression" is positive, 0 if zero, or -1 if negative.
smallint
Returns the small integer representation of a number.
soundex
Returns a 4 character string code obtained by systematically abbreviating words and names in "string_expression" according to phonetics. Can be used to determine if two strings sound the same. For example, does sound-of ('SMITH') = sound-of ('SMYTH').
space
Returns a string consisting of "integer_expression" spaces.
substr
Returns the substring of "string_expression" that starts at position "integer_expression1" for "integer_expression2" characters. The first character in "string_expression" is at position 1.
table_name
Returns an unqualified name of a table or view based on the object name in "string_expression1" and the schema name given in "string_expression2". It is used to resolve aliases.
table_schema
Returns the schema name portion of the two-part table or view name based on the object name in "string_expression1" and the schema name in "string_expression2". It is used to resolve aliases.
time
Returns a time from a value.
timestamp
Returns a timestamp from a value or a pair of values. "Expression1" must represent a date value, and "expression2" must represent a time value.
timestamp_iso
Returns a datetime in the ISO format (yyyy-mm-dd hh:mm:ss.nnnnnn) converted from the IBM format (yyyy-mm-dd-hh.mm.ss.nnnnnn). If "expression" is a time, it inserts the value of the CURRENT DATE for the date elements and zero for the fractional time element.
timestampdiff
Returns an estimated number of intervals of type "expression1" based on the difference between two timestamps. "Expression2" is the result of subtracting two timestamp types and converting the result to CHAR. Valid values of "expression1" are: 1 Fractions of a second; 2 Seconds; 4 Minutes; 8 Hours; 16 Days; 32 Weeks; 64 Months; 128 Quarters; 256 Years.
to_char
Returns the string representation of a timestamp with the format of "string_expression".
to_date
Converts "expression1" to a datetime value as specified by the format "string_expression2". The first argument "expression1" may be of type string or numeric.
translate
Returns "string_expression1" in which characters from "string_expression3" are translated to the equivalent characters in "string_expression2". "String_expression4" is a single character that is used to pad "string_expression2" if it is shorter than "string_expression3". If only "string_expression1" is present, then this function translates it to uppercase characters.
trunc
Returns "numeric_expression1" truncated to "numeric_expression2" places to the right of the decimal point. If "numeric_expression2" is negative, "numeric_expression1" is truncated to the absolute value of "numeric_expression2" places to the left of the decimal point.
truncate
Returns "numeric_expression1" truncated to "numeric_expression2" places to the right of the decimal point. If "numeric_expression2" is negative, "numeric_expression1" is truncated to the absolute value of "numeric_expression2" places to the left of the decimal point.
ucase
Returns "string_expression" with all lowercase characters shifted to uppercase.
value
Returns the first non-null argument (or null if all arguments are null). The Value function takes two or more arguments.
varchar
Returns a VARCHAR representation of expression, with length numeric_expression.
week
Returns the week of the year in "date_expression" as an integer value in the range 1 to 53.
year
Returns the year from "date_expression".

Parent topic: Using the expression editor