DB2 Scalar functions - Guia de referencia

SQL - Scalar Functions - List of supported built-in functions Db2 11.5

Este guia de referencia contém uma lista alfabética das funções internas suportadas pelo Db2 for z / OS.

Nome da função	Descrição
ABS or ABSVAL	Retorna o valor absoluto de seu argumento
ACOS	Retorna o arco cosseno de um argumento como um ângulo, expresso em radianos
ADD_DAYS¹	Retorna um valor datetime que representa o primeiro argumento mais um número especificado de dias
ADD_MONTHS	Retorna uma data que representa o argumento da data mais o argumento do número de meses
ADMIN_TASK_LIST	Retorna uma tabela com uma linha para cada uma das tarefas definidas na lista de tarefas do agendador de tarefas administrativas (task scheduler task list
ADMIN_TASK_OUTPUT	Retorna os valores dos parâmetros de saída e os conjuntos de resultados, se disponíveis. Se a tarefa que foi executada não for um procedimento armazenado ou o status de execução solicitado não estiver disponível, a função retornará uma tabela vazia.
ADMIN_TASK_STATUS	Retorna uma tabela com uma linha para cada tarefa na lista de tarefas do agendador de tarefas administrativas que contém o status da última vez que a tarefa foi executada
ARRAY_AGG	Retorna uma matriz (array) na qual cada valor do conjunto de entrada é atribuído a um elemento da matriz
ARRAY_DELETE	Retorna uma matriz com os elementos solicitados excluídos.
ARRAY_FIRST	Retorna o valor mínimo do índice da matriz de uma matriz.
ARRAY_LAST	Retorna o valor máximo do índice da matriz de uma matriz.
ARRAY_NEXT	Retorna o próximo valor maior de índice de matriz para uma matriz, relativo a um argumento de índice de matriz especificado.
ARRAY_PRIOR	Retorna o próximo valor menor de índice de matriz para uma matriz, relativo a um argumento de índice de matriz especificado.
ARRAY_TRIM	Retorna uma matriz após remover elementos do final de uma matriz comum.
ASCII	Retorna o valor do código ASCII do maior número de caracteres do argumento como um número inteiro
ASCII_CHR	Retorna o caractere que corresponde ao valor do código ASCII especificado pelo argumento
ASCII_STR or ASCIISTR	Retorna uma versão ASCII do argumento da sequência de caracteres ou gráficos
ASIN	Retorna o arco seno de um argumento como um ângulo, expresso em radianos
ATAN	Retorna o arco tangente de um argumento como um ângulo, expresso em radianos
ATANH	Retorna o arco hiperbólico tangente de um argumento como um ângulo, expresso em radianos
ATAN2	Retorna a tangente do arco das coordenadas x e y como um ângulo, expresso em radianos
AVG	Retorna a média de um conjunto de números
BLOB	Returns a BLOB representation of its argument
BLOCKING_THREADS	Returns a table with one row for each lock or claim that threads hold against the databases that are specified in the input parameter
BIGINT	Returns a big integer representation of its argument
BITAND, BITANDNOT, BITOR, BITXOR, and BITNOT	Return a corresponding base 10 integer value in a data type that is based on the data type of the input arguments
BINARY	Returns a fixed-length binary string representation of its argument
BTRIM¹	Removes the characters that are specified in a trim string from the beginning and end of a source string
CARDINALITY	Returns a value of that represents the number of elements of an array
CCSID_ENCODING	Returns the encoding scheme of a CCSID with a value of ASCII, EBCDIC, UNICODE, or UNKNOWN
CEILING or CEIL	Returns the smallest integer greater than or equal to the argument
CHAR	Returns a fixed-length character string representation of its argument
CHARACTER_LENGTH or CHAR_LENGTH	Returns the length of its argument in the number of string units that are specified
CHR	Returns the character that corresponds to the ASCII code value that is specified by the argument
CLOB	Returns a CLOB representation of the first argument
COALESCE	Returns the first argument in a set of arguments that is not null
COLLATION_KEY	Returns a string that represents the collation key of the argument in the specified collation
COMPARE_DECFLOAT	Returns a SMALLINT value that indicates whether two arguments are equal, or unordered, or whether one argument is greater than the other
CONCAT	Returns the concatenation of two strings
CONTAINS	Returns a result about whether or not a match was found during a search of a text search index
CORR or CORRELATION	Returns the coefficient of the correlation of a set of number pairs
COS	Returns the cosine of an argument that is expressed as an angle in radians
COSH	Returns the hyperbolic cosine of an argument that is expressed as an angle in radians
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
COVAR_POP or COVARIANCE or COVAR	Returns the population covariance of a set of number pairs
COVAR_SAMP or COVARIANCE_SAMP	Returns unbiased sample covariance (n-1) of a set of number pairs
CUME_DIST¹	Returns a cumulative distribution of a row in an OLAP window
CUME_DIST (aggregate)¹	Returns the cumulative distribution of a row that is hypothetically inserted into a group of rows
DATE	Returns a date derived from its argument
DAY	Returns the day part of its argument
DAYOFMONTH	Similar to DAY
DAYOFWEEK	Returns an integer in the range 1 - 7, where 1 represents Sunday
DAYOFWEEK_ISO	Returns an integer in the range 1 - 7, where 1 represents Monday
DAYOFYEAR	Returns an integer in the range 1 - 366, where 1 represents January 1
DAYS	Returns an integer representation of a date
DAYS_BETWEEN¹	Returns the number of full days between the specified arguments
DBCLOB	Returns a DBCLOB representation of its argument
DECIMAL or DEC	Returns a decimal representation of its argument
DECFLOAT	Returns a DECFLOAT representation of its argument
DECFLOAT_FORMAT	Returns a DECFLOAT(34) value that is based on the interpretation of the input string using the specified format
DECFLOAT_SORTKEY	Returns a binary value that can be used when sorting DECFLOAT values
DECODE	Returns a specified result-expression based on a comparison of input expressions (similar to the CASE expression)
DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB	Returns the decrypted value of an encrypted argument that was encrypted using the ENCRYPT_TDES function
DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT	Returns the decrypted value of an encrypted argument that was encrypted using the algorithm that was specified when the data was encrypted
DEGREES	Returns the number of degrees for an argument that is expressed in radians
DIFFERENCE	Returns a value that represents the difference between the sounds of two strings based on applying the SOUNDEX function to the strings
DIGITS	Returns a character string representation of a number
DOUBLE_PRECISION or DOUBLE	Returns a double precision floating-point representation of its argument
DSN_XMLVALIDATE	Returns an XML value that is the result of applying XML schema validation to the first argument
EBCDIC_CHR	Returns the character that corresponds to the EBCDIC code value that is specified by the argument
EBCDIC_STR	Returns an EBCDIC version of the string argument
ENCRYPT_DATAKEY	Returns the argument as an encrypted value using the specified key label and algorithm
ENCRYPT_TDES	Returns the argument as an encrypted value using the Triple DES encryption algorithm
EXP	Returns the exponential function of an argument
EXTRACT	Returns a portion of a date or timestamp based on its arguments
FIRST_VALUE¹	Returns the expression value for the first row in an OLAP window
FLOAT	Same as DOUBLE
FLOOR	Returns the largest integer that is less than or equal to the argument
GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY	Returns a character string of bit data that is unique compared to any other execution of the function
GETHINT	Returns the embedded password hint from encrypted data, if one exists
GETVARIABLE	Returns a varying-length character string representation of the value of a session variable
GRAPHIC	Returns a fixed-length graphic string representation of its argument
GREATEST	Returns the maximum value in a set of values
GROUPING	Returns a value that indicates if a row returned in a GROUP BY result is a row generated by a grouping set that excludes the column represented by its argument
HASH	Returns a varying-length value that is the result of applying the specified algorithm to the first argument. It is intended for cryptographic purposes.
HASH_MD5, HASH_SHA1, and HASH_SHA256	Return a fixed-length value that is the result of applying a hash algorithm to an input argument. They are intended for cryptographic purposes.
HEX	Returns a hexadecimal representation of its argument
HOUR	Returns the hour part of its argument
IDENTITY_VAL_LOCAL	Returns the most recently assigned value for an identity column
IFNULL	Returns the first argument in a set of two arguments that is not null
INSERT	Returns a string that is composed of an argument inserted into another argument at the same position where some number of bytes have been deleted
INSTR	Returns the starting position of the first occurrence of one string within another string
INTEGER or INT	Returns an integer representation of its argument
JULIAN_DAY	Returns an integer that represents the number of days from January 1, 4712 B.C.
LAG¹	Returns the expression value for the row at offset rows before the current row
LAST_DAY	Returns a date that represents the last day of the month of the date argument
LAST_VALUE¹	Returns the expression value for the last row in an OLAP window
LCASE	Returns a string with the characters converted to lowercase
LEAD¹	Returns the expression value for the row at offset rows after the current row
LEAST	Returns the minimum value in a set of values
LEFT	Returns a string that consists of the specified number of most bytes or the specified string units
LENGTH	Returns the length of its argument
LN	Returns the natural logarithm of an argument
LOCATE	Returns the starting position of one string within another string
LOCATE_IN_STRING	Returns the starting position of the first occurrence of one string within another string
LOG10	Returns the base 10 logarithm of an argument
LOWER	Returns a string with the characters converted to lowercase
LPAD	Returns a string that is padded on the with blanks or a specified string
LTRIM	Returns the characters of a string with the leading blanks or hexadecimal zeros removed
MAX (aggregate)	Returns the maximum value in a set of column values in a group
MAX (scalar)	Returns the maximum value in a set of values
MAX_CARDINALITY	Returns a value of type BIGINT that represents the maximum number of elements that an array can contain. This value is the cardinality that was specified in the CREATE TYPE statement for an ordinary array type.
MICROSECOND	Returns the microsecond part of its argument
MIDNIGHT_SECONDS	Returns an integer in the range 0 - 86400 that represents the number of seconds between midnight and the argument
MIN (aggregate)	Returns the minimum value in a set of values in a group
MIN (scalar)	Returns the minimum value in a set of values
MINUTE	Returns the minute part of its argument
MOD	Returns the remainder of one argument divided by a second argument
MONTH	Returns the month part of its argument
MONTHS_BETWEEN	Returns an estimate of the number of months between two arguments
MQREAD	Returns a message from a specified MQSeries® location (return value of VARCHAR) without removing the message from the queue
MQREADALL	Returns a table containing the messages and message metadata from a specified MQSeries location with a VARCHAR column and without removing the messages from the queue
MQREADALLCLOB	Returns a table containing the messages and message metadata from a specified MQSeries location with a CLOB column and without removing the messages from the queue
MQREADCLOB	Returns a message from a specified MQSeries location (return value of CLOB) without removing the message from the queue
MQRECEIVE	Returns a message from a specified MQSeries location (return value of VARCHAR) with removal of message from the queue
MQRECEIVEALL	Returns a table containing the messages and message metadata from a specified MQSeries location with a VARCHAR column and with removal of messages from the queue
MQRECEIVEALLCLOB	Returns a table containing the messages and message metadata from a specified MQSeries location with a CLOB column and with removal of messages from the queue
MQRECEIVECLOB	Returns a message from a specified MQSeries location (return value of CLOB) with removal of message from the queue
MQSEND	Sends data to a specified MQSeries location, and returns a varying-length character string that indicates whether the function was successful or unsuccessful
MULTIPLY_ALT	Returns the product of the two arguments as a decimal value, used when the sum of the argument precisions exceeds 31
NEXT_DAY	Returns a timestamp that represents the first weekday, specified by the second argument, after the date argument
NEXT_MONTH¹	Returns the first day of the next month after the specified date
NORMALIZE_DECFLOAT	Returns a DECFLOAT value that is the result of normalizing the input argument
NORMALIZE_STRING	Returns a string value that is the result of normalizing the input Unicode value
NTH_VALUE¹	Returns the expression value for the nth-row row in an OLAP window
NTILE¹	Returns the quantile rank of a row
NULLIF	Returns NULL if the arguments are equal; else the first argument
NVL	Returns the first argument that is not null
OVERLAY	Returns a string that is composed of an argument 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 argument
PERCENTILE_DISC	Returns a percentile of a set of values
PERCENT_RANK¹	Returns a relative percentile rank of a row in an OLAP window
PERCENT_RANK (aggregate)¹	Returns the relative percentile rank of a row that is hypothetically inserted into a group of rows
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 or POW	Returns the value of one argument raised to the power of a second argument
QUANTIZE	Returns a DECFLOAT value that is equal in value (except for any rounding) and sign to the first argument and which has an exponent set to be equal to the exponent of the second argument
QUARTER	Returns an integer in the range 1 - 4 that represents the quarter of the year for the date specified in the argument
RADIANS	Returns the number of radians for an argument that is expressed in degrees
RAISE_ERROR	Raises an error in the SQLCA with the specified SQLSTATE and error description
RANDOM or RAND	Returns a double precision floating-point random number
RATIO_TO_REPORT¹	Returns the ratio of an argument to the sum of the arguments in an OLAP partition
REAL	Returns a single precision floating-point representation of its argument
REGEXP_COUNT¹	Returns a count of the number of times that a regular expression pattern is matched in a string
REGEXP_INSTR¹	Returns the starting or ending position of the matched substring, depending on the value of the return_option argument
REGEXP_LIKE¹	Returns an INTEGER value of 0 or 1 indicating if the regular expression pattern is found in a string
REGEXP_REPLACE¹	Returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string
REGEXP_SUBSTR¹	Returns one occurrence of a substring of a string that matches the regular expression pattern
REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SXY, and REGR_SYY¹	Return quantities that can be used to compute various diagnostic statistics needed for the evaluation of the quality and statistical validity of a regression model
REGR_COUNT¹	Returns the number of non-null number pairs used to fit a regression line
REGR_INTERCEPT or REGR_ICPT¹	Returns the y-intercept of a regression line ("b" in the equation y = a * x + b)
REGR_R2¹	Returns the coefficient of determination ("R-squared" or "goodness-of-fit") for a regression
REGR_SLOPE¹	Returns the slope of a regression line ("a" in the equation y = a * x + b)
REPEAT	Returns a character string composed of an argument repeated a specified number of times
REPLACE	Returns a string in which all occurrences of an argument within a second argument are replaced with a third argument
RID	Returns the RID of a row
RIGHT	Returns a string that consists of the specified number of rightmost bytes or specified string units
ROUND	Returns a number rounded to the specified number of places to the right or of the decimal place
ROUND_TIMESTAMP	Returns a timestamp rounded to the unit 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 or hexadecimal zeros removed
SCORE	Returns a relevance score that measures how well a document matches the query used to search a text search index
SECOND	Returns the second part of its argument
SIGN	Returns the sign of an argument
SIN	Returns the sine of an argument that is expressed as an angle in radians
SINH	Returns the hyperbolic sine of an argument that is expressed as an angle in radians
SMALLINT	Returns a small integer representation of its argument
SOAPHTTPC and SOAPHTTPV	Returns a CLOB or VARCHAR representation of XML data from a request to a web service
SOAPHTTPNC and SOAPHTTPNV	Returns a complete CLOB or VARCHAR representation of XML data from a complete request to a web service
SOUNDEX	Returns a value that represents the sound of the words in the argument
SPACE	Returns a string that consists of the number of blanks the argument specifies
SQRT	Returns the square root of its argument
STDDEV_POP or STDDEV	Returns the population standard deviation (division by n) of a set of numbers
STDDEV_SAMP	Returns the sample standard deviation (division by n-1) of a set of numbers
STRIP	Returns the characters of a string with the blanks (or specified character) at the beginning, end, or both beginning and end of the string removed
STRLEFT	Returns a string that consists of the specified number of most bytes or the specified string units
STRPOS	Returns the position of the first occurrence of an argument within another argument
STRRIGHT	Returns a string that consists of the specified number of rightmost bytes or specified string units
SUBSTR	Returns a substring of a string
SUBSTRING	Returns a substring of a string using the specified string units
SUM	Returns the sum of a set of numbers
TAN	Returns the tangent of an argument that is expressed as an angle in radians
TANH	Returns the hyperbolic tangent of an argument that is expressed as an angle in radians
TIME	Returns a time derived from its argument
TIMESTAMP	Returns a timestamp derived from its arguments
TIMESTAMPADD	Returns a timestamp derived from adding the specified interval to a timestamp
TIMESTAMP_FORMAT	Returns a timestamp for a character string expression, using a specified format to interpret the string
TIMESTAMP_ISO	Returns a timestamp derived from its arguments
TIMESTAMPDIFF	Returns an estimated number of the specified intervals based on the difference between two timestamps
TIMESTAMP_TZ	Returns a timestamp with a time zone derived from its arguments
TO_CHAR	Returns a character string representation of a timestamp value that has been formatted using a specified character template
TO_CLOB	Returns a CLOB representation of the first argument
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 inputstring using the specified format
TOTALORDER	Returns a SMALLINT value that indicates the comparison order of two arguments
TO_TIMESTAMP	Returns a timestamp for a character string expression, using a specified format to interpret the string
TRANSLATE	Returns a string with one or more characters translated
TRIM	Removes bytes from the beginning, from the end, or from both the beginning and end of a string expression
TRIM_ARRAY	Returns an array after removing elements from the end of an ordinary array.
TRUNCATE or TRUNC	Returns a number truncated to the specified number of places to the right or of the decimal point
TRUNC_TIMESTAMP	Returns a timestamp truncated to the unit specified by the timestamp format string
UCASE	Returns a string with the characters converted to uppercase
UNICODE	Returns the Unicode (UTF-16) code value of the most character of the argument as an integer
UNICODE_STR or UNICODE_STR	Returns a string in Unicode (UTF-8 or UTF-16) that represents a Unicode encoding of the argument
UNPACK	Returns a row of values that are derived from unpacking the input binary string. It is used to unpack a string that was encoded according to the PACK function
UPPER	Returns a string with the characters converted to uppercase
VALUE	Same as COALESCE
VARBINARY	Returns a varying-length binary string representation of its argument
VARCHAR	Returns the varying-length character string representation of its argument
VARCHAR9	Returns the fixed-length character string representation of its argument
VARCHAR_FORMAT	Returns a varying-length character string representation of a timestamp, with the string in a specified format
VARGRAPHIC	Returns a varying-length graphic string representation of its argument
VAR_POP or VARIANCE or VAR	Returns the biased variance (division by n) of a set of numbers
VAR_SAMP or VARIANCE_SAMP	Returns the sample variance (division by n-1) of a set of numbers
VERIFY_GROUP_FOR_USER	Returns a value that indicates whether the primary authorization ID and the group authorization IDs that are associated with the first argument are included in the authorization names that are specified in the list of the second argument.
VERIFY_ROLE_FOR_USER	Returns a value that indicates whether the roles that are associated with the first argument are included in the role names that are specified in the list of the second argument
VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER	Returns a value that indicates whether the authorization ID that is associated with first argument has acquired a role in a trusted connection and whether that acquired role is included in the role names that are specified in the list of the second argument
WEEK	Returns an integer that represents the week of the year with Sunday as the first day of the week
WEEK_ISO	Returns an integer that represents the week of the year with Monday as first day of a week
XMLAGG	Returns an XML type that represents a concatenation of XML elements from a collection of XML elements
XMLATTRIBUTES	Returns an XML sequence that contains an XQuery attribute node for each non-null argument
XMLCOMMENT	Returns an XML value with a single comment node from a string expression
XMLCONCAT	Returns an XML value that represents a forest of XML elements generated by concatenating a variable number of arguments
XMLDOCUMENT	Returns an XML value with a single document node and zero or more nodes as its children
XMLELEMENT	Returns an XML value that represents an XML element
XMLFOREST	Returns an XML value that represents a forest of XML elements that all share a specific pattern
XMLMODIFY	Returns an XML value that might have been modified by the evaluation of an XQuery updating expression and XQuery variables that are specified as input arguments
XMLNAMESPACES	Returns the declaration of one or more XML namespaces
XMLPARSE	Returns an XML value from parsing the argument as an XML document
XMLPI	Returns an XML value with a single processing instruction node
XMLQUERY	Returns an XML value from the evaluation of an XPath expression against a set of arguments
XMLSERIALIZE	Returns an SQL character string or a BLOB value from an XML value
XMLTABLE	Returns a result table from the evaluation of XQuery expressions, possibly using specified input arguments as XQuery variables
XMLTEXT	Returns an XML value with a single text node that contains the value of the argument
XMLVALIDATE	The XMLVALIDATE function returns a copy of the input XML value augmented with information obtained from XML schema validation, including default values.
XMLXSROBJECTID	Returns the XSR object identifier of the XML schema that is used to validate the XML document specified in the argument
XSLTRANSFORM	The XSLTRANSFORM function transforms an XML document into a different data format. The output can be any form possible for the XSLT processor, including but not limited to XML, HTML, and plain text. Note: This is a Db2-supplied user-defined function.
YEAR	Returns the year part of its argument
YEARS_BETWEEN	The YEARS_BETWEEN function returns the number of full years between the specified arguments.
YMD_BETWEEN	The YMD_BETWEEN function returns a numeric value that specifies the number of full years, full months, and full days between two datetime values.