Built-in SQL Functions

This section contains the reference documentation for the SQL Functions that are built into Splice Machine, which are grouped into the following subsections:

Conversion Functions

These are the built-in conversion functions:

Function Name Description
BIGINT Returns a 64-bit integer representation of a number or character string in the form of an integer constant.
CAST Converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value.
CHAR Returns a fixed-length character string representation.
DOUBLE Returns a floating-point number
INTEGER Returns an integer representation of a number or character string in the form of an integer constant.
SMALLINT Returns a small integer representation of a number or character string in the form of a small integer constant.
TO_CHAR Formats a date value into a string.
TO_DATE Formats a date string according to a formatting specification, and returns a date value.
VARCHAR Returns a varying-length character string representation of a character string.

Current Session Functions

These are the built-in current session functions:

Function Name Description
CURRENT_ROLE Returns the authorization identifier of the current role.
CURRENT SCHEMA Returns the schema name used to qualify unqualified database object references.
CURRENT_USER Depending on context, returns the authorization identifier of either the user who created the SQL session or the owner of the schema.
SESSION_USER Depending on context, returns the authorization identifier of either the user who created the SQL session or the owner of the schema.
USER Depending on context, returns the authorization identifier of either the user who created the SQL session or the owner of the schema.

Date and Time Functions

These are the built-in date and time functions:

Function Name Description
ADD_MONTHS Returns the date resulting from adding a number of months added to a specified date.
CURRENT_DATE Returns the current date.
CURRENT_TIME Returns the current time;
CURRENT_TIMESTAMP Returns the current timestamp;
DATE Returns a date from a value.
DAY Returns the day part of a value.
EXTRACT Extracts various date and time components from a date expression.
HOUR Returns the hour part of a value.
LAST_DAY Returns the date of the last day of the specified month.
MINUTE Returns the minute part of a value.
MONTH Returns the numeric month part of a value.
MONTH_BETWEEN Returns the number of months between two dates.
MONTHNAME Returns the string month part of a value.
NEXT_DAY Returns the date of the next specified day of the week after a specified date.
NOW Returns the current date and time as a timestamp value.
QUARTER Returns the quarter number (1-4) from a date expression.
SECOND Returns the seconds part of a value.
TIME Returns a time from a value.
TIMESTAMP Returns a timestamp from a value or a pair of values.
TIMESTAMPADD Adds the value of an interval to a timestamp value and returns the sum as a new timestamp
TIMESTAMPDIFF Finds the difference between two timestamps, in terms of the specfied interval.
TO_CHAR Formats a date value into a string.
TO_DATE Formats a date string according to a formatting specification, and returns a date value.
TRUNC or TRUNCATE Truncates numeric, date, and timestamp values.
WEEK Returns the year part of a value.
YEAR Returns the year part of a value.

Miscellaneous Functions

These are the built-in miscellaneous functions:

Function Name Description
COALESCE Takes two or more compatible arguments and Returns the first argument that is not null.
NULLIF Returns NULL if the two arguments are equal, and it Returns the first argument if they are not equal.
NVL Takes two or more compatible arguments and Returns the first argument that is not null.
ROWID A pseudocolumn that uniquely defines a single row in a database table.

Numeric Functions

These are the built-in numeric functions:

Function Name Description
ABS or ABSVAL Returns the absolute value of a numeric expression.
CEIL or CEILING Round the specified number up, and return the smallest number that is greater than or equal to the specified number.
EXP Returns e raised to the power of the specified number.
FLOOR Rounds the specified number down, and Returns the largest number that is less than or equal to the specified number.
LN or LOG Return the natural logarithm (base e) of the specified number.
LOG10 Returns the base-10 logarithm of the specified number.
MOD Returns the remainder (modulus) of one number divided by another.
RAND Returns a random number given a seed number
RANDOM Returns a random number.
SIGN Returns the sign of the specified number.
SQRT Returns the square root of a floating point number; 
TRUNC or TRUNCATE Truncates numeric, date, and timestamp values.

String Functions

These are the built-in string functions:

Function Name Description
Concatenate Concatenates a character string value onto the end of another character string. Can also be used on bit string values.
INITCAP Converts the first letter of each word in a string to uppercase, and converts any remaining characters in each word to lowercase.
INSTR Returns the index of the first occurrence of a substring in a string.
LCASE or LOWER Takes a character expression as a parameter and Returns a string in which all alpha characters have been converted to lowercase.
LENGTH Applied to either a character string expression or a bit string expression and Returns the number of characters in the result.
LOCATE Used to search for a string within another string. 
LTRIM Removes blanks from the beginning of a character string expression.
REGEXP_LIKE Returns true if a string matches a regular expression.
REPLACE Replaces all occurrences of a substring with another substring
RTRIM Removes blanks from the end of a character string expression.
SUBSTR Return a portion of string beginning at the specified position for the number of characters specified or rest of the string.
TRIM Takes a character expression and Returns that expression with leading and/or trailing pad characters removed. 
UCASE or UPPER Takes a character expression as a parameter and Returns a string in which all alpha characters have been converted to uppercase.

Trigonometric Functions

These are the built-in trigonometric functions:

Function Name Description
ACOS Returns the arc cosine of a specified number.
ASIN Returns the arc sine of a specified number.
ATAN Returns the arc tangent of a specified number.
ATAN2 Returns the arctangent, in radians, of the quotient of the two arguments.
COS Returns the cosine of a specified number.
COSH Returns the hyperbolic cosine of a specified number.
COT Returns the cotangens of a specified number.
DEGREES Converts a specified number from radians to degrees.
PI Returns a value that is closer than any other value to pi.
RADIANS Converts a specified number from degrees to radians.
SIN Returns the sine of a specified number.
SINH Returns the hyperbolic sine of a specified number.
TAN Returns the tangent of a specified number.
TANH Returns the hyperbolic tangent of a specified number

Window and Aggregate Functions

These are the built-in window and aggregate functions

Function Name Description
AVG Returns the average computed over a subset (partition) of a table.
COUNT Returns the number of rows in a partition.
DENSE_RANK Returns the ranking of a row within a partition.
FIRST_VALUE Returns the first value within a partition..
LAG Returns the value of an expression evaluated at a specified offset number of rows before the current row in a partition.
LAST_VALUE Returns the last value within a partition..
LEAD Returns the value of an expression evaluated at a specified offset number of rows after the current row in a partition.
MAX Returns the maximum value computed over a partition.
MIN Returns the minimum value computed over a partition.
RANK Returns the ranking of a row within a subset of a table.
ROW_NUMBER Returns the row number of a row within a partition.
STDDEV_POP Returns the population standard deviation of a set of numeric values
STDDEV_SAMP Returns the sample standard deviation of a set of numeric values
SUM Returns the sum of a value calculated over a partition.



For access to the source code for the Community Edition of Splice Machine, visit our open source GitHub repository.