DB2 BUILT-IN SQL FUNCTIONS

IBMMainframes.com

Quick References for IBM Mainframe Programming

DB2 BUILT-IN SQL FUNCTIONS SQL COLUMN FUNCTIONS:
AVG	Returns the Average of a set of numbers
CORRELATION or CORR	Returns the Coefficient of the correlation of a set of number pairs
COUNT	Returns the number of rows or values in a set of rows or values
COUNT_BIG	Same as COUNT, except the result can be greater than the maximum value of an integer
COVARIANCE	Returns the covariance (population) of a set of number pairs
MAX	Returns the maximum value in a set of values
MIN	Returns the minimum value in a set of values
MEDIAN	Returns the median of a set of numbers, The median of a set of data is the middlemost number in the set
STDDEV	Returns the standard deviation of a set of numbers
SUM	Returns the sum of a set of numbers
VARIANCE or VAR	Returns the variance of a set of numbers
XMLAGG	Returns an XML sequence that contains an item for each non-null value in a set of XML values.

SQL SCALAR FUNCTIONS:
ABS	Returns the absolute value of its argument
ACOS	Returns the arc cosine of an argument as an angle, expressed in radians, The ACOS and COS functions are inverse operations.
ADD_MONTHS	Returns a date that represents expression plus a specified number of months.
ASIN	Returns the arc sine of the argument as an angle, expressed in radians. The ASIN and SIN functions are inverse operations.
ATAN	Returns the arc tangent of the argument as an angle, expressed in radians. The ATAN and TAN functions are inverse operations.
BIGINT	Returns a big integer representation of either a number or a character or graphic string representation of a number.
BLOB	Returns a BLOB representation of a string of any type or of a row ID type.
CEIL or CEILING	Returns the smallest integer value that is greater than or equal to the argument.
CHAR	Returns a fixed-length character string representation of the argument.
CHARACTER_LENGTH	Returns the length of the first argument in the specified string unit.
CLOB	Returns a CLOB representation of a string.
COALESCE	Returns the value of the first nonnull expression.
COLLATION_KEY	Returns a varying-length binary string that represents the collation key of the argument in the specified collation.
COMPARE_DECFLOAT	Returns a SMALLINT value that indicates whether the two arguments are equal or unordered, or whether one argument is greater than the other.
CONCAT	Returns the concatenation of two strings.
COS	Returns the cosine of the argument, where the argument is an angle, expressed in radians. The COS and ACOS functions are inverse operations.
COSH	Returns the hyperbolic cosine of the argument, where the argument is an angle, expressed in radians.
DATE	Returns a date that is derived from a value.
DAY	Returns the day part of a value.
DAYOFMONTH	Returns the day part of a value. The function is similar to the DAY function, except DAYOFMONTH does not support a date or timestamp duration as an argument.
DAYOFWEEK	Returns an integer, in the range of 1 to 7, that represents the day of the week, where 1 is Sunday and 7 is Saturday.
DAYOFYEAR	Returns an integer, in the range of 1 to 366, that represents the day of the year, where 1 is January 1.
DAYS	Returns an integer representation of a date.
DBCLOB	Returns a DBCLOB representation of a character string value (with the single-byte characters converted to double-byte characters) or a graphic string value.
DECFLOAT	Returns a decimal floating-point representation of either a number or a character string representation of a number, a decimal number, an integer or a floating-point number.
DECIMAL	Returns a decimal representation of either a number or a character-string or graphic-string representation of a number, an integer, or a decimal number.
DECODE	Returns the absolute value of its argument
DEGREES	Returns the number of degrees of the argument, which is an angle, expressed in radians.
DIFFERENCE	Returns a value, from 0 to 4, that represents the difference between the sounds of two strings, based on applying the SOUNDEX function to the strings. A value of 4 is the best possible sound match.
DIGITS	Returns a character string representation of the absolute value of a number.
DOUBLE_PRECISION or DOUBLE	Returns a floating-point representation of either a number or a character-string or graphic-string representation of a number, an integer, a decimal number, or a floating-point number.
DSN_XMLVALIDATE	Returns an XML value that is the result of applying XML schema validation to the first argument of the function.
EXP	Returns a value that is the base of the natural logarithm (e), raised to a power that is specified by the argument. The EXP and LN functions are inverse operations.
EXTRACT	Returns a portion of a date or timestamp, based on its arguments.
FLOAT	Returns a floating-point representation of either a number or a string representation of a number. FLOAT is a synonym for the DOUBLE function.
FLOOR	Returns the largest integer value that is less than or equal to the argument.
GENERATE_UNIQUE	Returns a bit data character string that is unique, compared to any other execution of the same function.
GETVARIABLE	Returns a varying-length character-string representation of the current value of the session variable that is identified by the argument.
GRAPHIC	Returns a fixed-length graphic-string representation of a character string or a graphic string value, depending on the type of the first argument.
HEX	Returns a hexadecimal representation of a value.
HOUR	Returns the hour part of a value.
IFNULL	Returns the first nonnull expression.
INSERT	Returns a string where, beginning at start in source-string, length characters have been deleted and insert-string has been inserted.
INTEGER or INT	Returns an integer representation of either a number or a character string or graphic string representation of an integer.
JULIAN_DAY	Returns an integer value that represents a number of days from January 1, 4713 B.C. (the start of the Julian date calendar) to the date that is specified in the argument.
LAST_DAY	Returns a date that represents the last day of the month of the date argument.
LCASE or LOWER	Returns a string in which all the characters are converted to lowercase characters.
LEFT	Returns a string that consists of the specified number of leftmost bytes of the specified string units.
LENGTH	Returns the length of a value.
LN	Returns the natural logarithm of the argument. Same as LOG function of the old DB2 versions. The LN and EXP functions are inverse operations.
LOCATE	Returns the position at which the first occurrence of an argument starts within another argument.
LOCATE_IN_STRING	Returns the position at which an argument starts within a specified string.
LOG10	Returns the common logarithm (base 10) of a number.
LPAD	Returns a string that is composed of string-expression that is padded on the left, with pad or blanks. The LPAD function treats leading or trailing blanks in string-expression as significant.
LTRIM	Returns the characters of a string with the leading blanks removed
MAX	Returns the maximum value in a set of values.
MICROSECOND	Returns the microsecond part of a value.
MIDNIGHT_SECONDS	Returns an integer, in the range of 0 to 86400, that represents the number of seconds between midnight and the time that is specified in the argument.
MIN	Returns the minimum value in a set of values.
MINUTE	Returns the minute part of a value.
MOD	Returns the remainder of one argument divided by second argument.
MONTH	Returns the month part of a value.
MONTHS_BETWEEN	Returns an estimate of the number of months between two arguments.
MQREAD	Returns a message from a specified MQSeries location without removing the message from the queue.
MQRECEIVE	Returns a message from a specified MQSeries location and removes the message from the queue.
MQSEND	Returns a varying-length character string that indicates whether the function of sending data to a specified MQSeries location was successful or unsuccessful.
MULTIPLY_ALT	Returns the product of the two arguments. This function is an alternative to the multiplication operator and is especially useful when the sum of the precisions of the arguments exceeds 31.
NEXT_DAY	Returns a datetime value that represents the first weekday, named by string-expression, that is later than the date in expression.
NORMALIZE_STRING	Returns a normalized string that can be used for comparison
NULLIF	Returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.
NVL	Returns the first argument that is not null.
OVERLAY	Returns a string that is composed of one argument that is inserted into another argument at the same position where some number of bytes have been deleted.
PACK	Returns a binary string value that contains a data type array and a packed representation of each non-null expression argument.
POSITION	Returns the position of the first occurrence of an argument within another argument, where the position is expressed in terms of the string units that are specified.
POSSTR	Returns the position of the first occurrence of an argument within another argument.
POWER	Returns the value of the first argument to the power of the second argument. (X^y)
QUANTIZE	Returns a DECFLOAT value that is equal in value (except for any rounding) and sign to the first argument and that has an exponent that is set to equal the exponent of the second argument.
QUARTER	Returns an integer between 1 and 4 that represents the quarter of the year in which the date resides. For example, any dates in January, February, or March return the integer 1.
RADIANS	Returns the number of radians for an argument that is expressed in degrees.
RAISE_ERROR	Returns an error with the specified SQLSTATE (along with SQLCODE -438) and error condition. The RAISE_ERROR function always returns the null value with an undefined data type.
RAND	Returns a random floating-point value between 0 and 1. An argument can be specified as an optional seed value.
REAL	Returns a single-precision floating-point representation of either a number or a string representation of a number.
REPEAT	Returns a character string that is composed of an argument that is repeated a specified number of times.
REPLACE	Returns a string in which all occurrences of an search-string within a source-string replaced with a replace-string. If search-string is not found in source-string, source-string is returned unchanged.
RID	Returns the record ID (RID) of a row. The RID is used to uniquely identify a row.
RIGHT	Returns a string that consists of the specified number of rightmost bytes or specified string unit from a string
ROUND	Returns a number that is rounded to the specified number of places to the right or left of the decimal place.
ROUND_TIMESTAMP	Returns a timestamp that is rounded to the unit that is specified by the timestamp format string.
ROWID	Returns a row ID representation of its argument.
RPAD	Returns a string that is padded on the right with blanks or a specified string.
RTRIM	Returns the characters of an argument with the trailing blanks removed.
SECOND	Returns the seconds part of a value with optional fractional seconds.
SIGN	Returns an indicator of the sign of the argument. (-1 / 0 / 1)
SIN	Returns the sine of the argument, where the argument is an angle, expressed in radians.
SINH	Returns the hyperbolic sine of the argument, where the argument is an angle, expressed in radians.
SMALLINT	Returns a small integer representation either of a number or of a string representation of a number.
SOUNDEX	Returns a 4-character code that represents the sound of the words in the argument. The result can be compared to the results of the SOUNDEX function of other strings.
SPACE	Returns a character string that consists of the number of SBCS blanks that the argument specifies.
SQRT	Returns the square root of the argument.
STRIP	Returns a string with the blanks at the beginning, end, or both of the string removed.
SUBSTR or SUBSTRING	Returns a substring of a string.
TAN	Returns the tangent of the argument, where the argument is an angle, expressed in radians.
TANH	Returns the hyperbolic tangent of the argument, where the argument is an angle, expressed in radians.
TIME	Returns a time that is derived from a value.
TIMESTAMP	Returns a TIMESTAMP WITHOUT TIME ZONE value from its argument or arguments.
TIMESTAMPADD	Returns the result of adding the specified number of the designated interval to the timestamp value.
TIMESTAMP_FORMAT	Returns a TIMESTAMP WITHOUT TIME ZONE value that is based on the interpretation of the input string using the specified format.
TIMESTAMPDIFF	Returns an estimated number of intervals of the type that is defined by the first argument, based on the difference between two timestamps.
TIMESTAMP_TZ	Returns a TIMESTAMP WITH TIME ZONE value from the input arguments.
TO_CHAR	Returns a character string representation of a timestamp value that has been formatted using a specified character template.
TO_DATE	Returns a timestamp value that is based on the interpretation of the input string using the specified format.
TO_NUMBER	Returns a DECFLOAT(34) value that is based on the interpretation of the input string using the specified format.
TOTALORDER	Returns an ordering for DECFLOAT values. The TOTALORDER function returns a small integer value that indicates how expression1 compares with expression2.
TRANSLATE	Returns a value in which one or more characters of the first argument might have been converted to other characters.
TRIM	Returns a string with the specified bytes at the beginning, end, or both of the string removed.
TRUNCATE or TRUNC	Returns a number truncated to the specified spaces to the right/left of the decimal
UCASE or UPPER	Returns a string in which all the characters have been converted to uppercase characters, based on the CCSID of the argument.
UNICODE	Returns the Unicode UTF-16 code value of the leftmost character of the argument as an integer.
UNICODE_STR	Returns a string in Unicode UTF-8 or UTF-16, depending on the specified option.
VALUE	Returns the value of the first non-null expression. Same as COALESCE.
VARBINARY	Returns a varying-length binary string representation of a string of any type.
VARCHAR	Returns a varying-length character string representation of the value specified by the first argument.
VARCHAR_FORMAT	Returns a character string representation of the first argument in the format indicated by format-string.
VARGRAPHIC	Returns a varying-length graphic string representation of a the first argument. The first argument can be a character string value or a graphic string value.
WEEK	Returns an integer in the range of 1 to 54 that represents the week of the year. The week starts with Sunday, and January 1 is always in the first week.
YEAR	Returns the year part of a value that is a character or graphic string. The value must be a valid string representation of a date or timestamp.
References: IBM^® Manuals