# Built-in functions

A built-in function is an expression in which an SQL keyword or special operator executes some operation.

Built-in functions use keywords or special built-in operators. Built-ins are SQLIdentifiers and are case-insensitive. Note that escaped functions like TIMESTAMPADD and TIMESTAMPDIFF are only accessible using the JDBC escape function syntax, and can be found in JDBC escape syntax.

- Standard built-in functions
  The standard built-in functions supported in Derby are as follows.
- Aggregates (set functions)
  This section describes aggregates (also described as set functions in ANSI SQL and as column functions in some database literature).
- ABS or ABSVAL function
  The ABS or ABSVAL function returns the absolute value of a numeric expression.
- ACOS function
  The ACOS function returns the arc cosine of a specified number.
- ASIN function
  The ASIN function returns the arc sine of a specified number.
- ATAN function
  The ATAN function returns the arc tangent of a specified number.
- ATAN2 function
  The ATAN2 function returns the arctangent, in radians, of the quotient of the two arguments.
- AVG function
  AVG is an aggregate function that evaluates the average of an expression over a set of rows.
- BIGINT function
  The BIGINT function returns a 64-bit integer representation of a number or character string in the form of an integer constant.
- CAST function
  The CAST function converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value.
- CEIL or CEILING function
  The CEIL or CEILING function rounds the specified number up, and returns the smallest number that is greater than or equal to the specified number.
- CHAR function
  The CHAR function returns a fixed-length character string representation.
- COALESCE function
  The COALESCE function takes two or more compatible arguments and returns the first argument that is not null.
- Concatenation operator
  The concatenation operator, ||, concatenates its right operand to the end of its left operand. It operates on character or bit expressions.
- COS function
  The COS function returns the cosine of a specified number.
- COSH function
  The COSH function returns the hyperbolic cosine of a specified number.
- COT function
  The COT function returns the cotangent of a specified number.
- COUNT function
  COUNT is an aggregate function that counts the number of rows accessed in an expression. COUNT is allowed on all types of expressions.
- COUNT(*) function
  COUNT(*) is an aggregate function that counts the number of rows accessed. No NULLs or duplicates are eliminated. COUNT(*) does not operate on an expression.
- CURRENT DATE function
  CURRENT DATE is a synonym for CURRENT_DATE.
- CURRENT_DATE function
  The CURRENT_DATE function returns the current date; the value returned does not change if it is executed more than once in a single statement.
- CURRENT ISOLATION function
  The CURRENT ISOLATION function returns the current isolation level as a CHAR(2) value of either "" (blank), "UR", "CS", "RS", or "RR".
- CURRENT_ROLE function
  The CURRENT_ROLE function returns the authorization identifier of the current role. If there is no current role, it returns NULL.
- CURRENT SCHEMA function
  The CURRENT SCHEMA function returns the schema name used to qualify unqualified database object references.
- CURRENT TIME function
  CURRENT TIME is a synonym for CURRENT_TIME.
- CURRENT_TIME function
  The CURRENT_TIME function returns the current time; the value returned does not change if it is executed more than once in a single statement.
- CURRENT TIMESTAMP function
  CURRENT TIMESTAMP is a synonym for CURRENT_TIMESTAMP.
- CURRENT_TIMESTAMP function
  The CURRENT_TIMESTAMP function returns the current timestamp; the value returned does not change if it is executed more than once in a single statement.
- CURRENT_USER function
  When used outside stored routines, the CURRENT_USER, USER, and SESSION_USER functions all return the authorization identifier of the user that created the SQL session.
- DATE function
  The DATE function returns a date from a value.
- DAY function
  The DAY function returns the day part of a value.
- DEGREES function
  The DEGREES function converts a specified number from radians to degrees.
- DOUBLE function
  The DOUBLE function returns a floating-point number corresponding to a number or a character string.
- EXP function
  The EXP function returns e raised to the power of the specified number.
- FLOOR function
  The FLOOR function rounds the specified number down, and returns the largest number that is less than or equal to the specified number.
- HOUR function
  The HOUR function returns the hour part of a value.
- IDENTITY_VAL_LOCAL function
  The IDENTITY_VAL_LOCAL function is a non-deterministic function that returns the most recently assigned value of an identity column for a connection, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause or a single row UPDATE statement.
- INTEGER function
  The INTEGER function returns an integer representation of a number or character string in the form of an integer constant.
- LCASE or LOWER function
  The LCASE or LOWER function takes a character expression as a parameter and returns a string in which all alphabetical characters have been converted to lowercase.
- LENGTH function
  The LENGTH function is applied to either a character string expression or a bit string expression and returns the number of characters in the result.
- LN or LOG function
  The LN and LOG functions return the natural logarithm (base e) of the specified number.
- LOG10 function
  The LOG10 function returns the base-10 logarithm of the specified number.
- LOCATE function
  The LOCATE function is used to search for a string within another string. If the desired string is found, LOCATE returns the index at which it is found. If the desired string is not found, LOCATE returns 0.
- LTRIM function
  The LTRIM function removes blanks from the beginning of a character string expression.
- MAX function
  MAX is an aggregate function that evaluates the maximum of an expression over a set of rows.
- MIN function
  MIN is an aggregate function that evaluates the minimum of an expression over a set of rows.
- MINUTE function
  The MINUTE function returns the minute part of a value.
- MOD function
  The MOD function returns the remainder (modulus) of argument 1 divided by argument 2. The result is negative only if argument 1 is negative.
- MONTH function
  The MONTH function returns the month part of a value.
- NULLIF function
  The NULLIF function can be used for conditional expressions in Derby.
- PI function
  The PI function returns a value that is closer than any other value to pi.
- RADIANS function
  The RADIANS function converts a specified number from degrees to radians.
- RANDOM function
  The RANDOM function returns a random number.
- RAND function
  The RAND function returns a random number given a seed number
- ROW_NUMBER function
  The ROW_NUMBER function returns the row number over a named or unnamed window specification.
- RTRIM function
  The RTRIM function removes blanks from the end of a character string expression.
- SECOND function
  The SECOND function returns the seconds part of a value.
- SESSION_USER function
  When used outside stored routines, the SESSION_USER, USER, and CURRENT_USER functions all return the authorization identifier of the user that created the SQL session.
- SIGN function
  The SIGN function returns the sign of the specified number.
- SIN function
  The SIN function returns the sine of a specified number.
- SINH function
  The SINH function returns the hyperbolic sine of a specified number.
- SMALLINT function
  The SMALLINT function returns a small integer representation of a number or character string in the form of a small integer constant.
- SQRT function
  The SQRT function returns the square root of a floating-point number; only the built-in types REAL, FLOAT, and DOUBLE PRECISION are supported.
- SUBSTR function
  The SUBSTR function acts on a character string expression or a bit string expression.
- STDDEV_POP function
  STDDEV_POP is an aggregate function that evaluates the standard deviation of an expression over a population.
- STDDEV_SAMP function
  STDDEV_SAMP is an aggregate function that evaluates the sample deviation of an expression over a set of rows.
- SUM function
  SUM is an aggregate function that evaluates the sum of the expression over a set of rows.
- TAN function
  The TAN function returns the tangent of a specified number.
- TANH function
  The TANH function returns the hyperbolic tangent of a specified number.
- TIME function
  The TIME function returns a time from a value.
- TIMESTAMP function
  The TIMESTAMP function returns a timestamp from a value or a pair of values.
- TRIM function
  The TRIM function takes a character expression and returns that expression with leading and/or trailing pad characters removed.
- UCASE or UPPER function
  The UCASE or UPPER function takes a character expression as a parameter and returns a string in which all alphabetical characters have been converted to uppercase.
- USER function
  When used outside stored routines, the USER, CURRENT_USER, and SESSION_USER functions all return the authorization identifier of the user that created the SQL session.
- VAR_POP function
  VAR_POP is an aggregate function that evaluates the population variance of an expression over a set of rows.
- VAR_SAMP function
  VAR_SAMP is an aggregate function that evaluates the sample variance of an expression over a set of rows.
- VARCHAR function
  The VARCHAR function returns a varying-length character string representation of a character string.
- XMLEXISTS operator
  XMLEXISTS is an SQL/XML operator that you can use to query XML values in SQL.
- XMLPARSE operator
  XMLPARSE is a SQL/XML operator that you use to parse a character string expression into a Derby XML value.
- XMLQUERY operator
  XMLQUERY is a SQL/XML operator that you can use to query XML values in SQL.
- XMLSERIALIZE operator
  XMLSERIALIZE is a SQL/XML operator that you can use to convert an XML type to a character type. There is no other way to convert the type of a Derby XML value.
- YEAR function
  The YEAR function returns the year part of a value.

Parent topic: SQL language reference
Related concepts
SQL identifiers
Statements
Built-in system functions
Built-in system procedures
Data types
Related reference
Capitalization and special characters
SQL clauses
SQL expressions
JOIN operations
SQL queries
SYSCS_DIAG diagnostic tables and functions