# FunctionProvider

abstract class FunctionProvider

Provides definitions for all the supported SQL functions. By default, definitions from the SQL standard are provided but if a vendor doesn't support a specific function, or it is implemented differently, the corresponding function should be overridden.

## Constructors

FunctionProvider
Link copied to clipboard
constructor()

## Types

MatchMode
Link copied to clipboard
interface MatchMode

Marker interface for the possible pattern matching modes.

## Properties

DEFAULT_VALUE_EXPRESSION
Link copied to clipboard
open val DEFAULT_VALUE_EXPRESSION: String

## Functions

arraySlice
Link copied to clipboard
open fun <T> arraySlice(expression: Expression<T>, lower: Int?, upper: Int?, queryBuilder: QueryBuilder)

SQL function that returns a subarray of elements stored from between lower and upper bounds (inclusive), or `null` if the stored array itself is null.

cast
Link copied to clipboard
open fun cast(expr: Expression<*>, type: IColumnType<*>, builder: QueryBuilder)

SQL function that casts an expression to a specific type.

charLength
Link copied to clipboard
open fun <T : String?> charLength(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that returns the length of expr, measured in characters, or `null` if expr is null.

concat
Link copied to clipboard
open fun concat(separator: String, queryBuilder: QueryBuilder, vararg expr: Expression<*>)

SQL function that concatenates multiple string expressions together with a given separator.

date
Link copied to clipboard
open fun <T> date(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the date field from a given temporal expression.

day
Link copied to clipboard
open fun <T> day(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the day field from a given date. The returned value is a number between 1 and 31 both inclusive.

delete
Link copied to clipboard
open fun delete(ignore: Boolean, table: Table, where: String?, limit: Int?, transaction: Transaction): String

Returns the SQL command that deletes one or more rows of a table.

open fun delete(ignore: Boolean, targets: Join, targetTables: List<Table>, where: Op<Boolean>?, limit: Int?, transaction: Transaction): String

Returns the SQL command that deletes one or more rows from a table in a join relation.

explain
Link copied to clipboard
open fun explain(analyze: Boolean, options: String?, internalStatement: String, transaction: Transaction): String

Returns the SQL command that obtains information about a statement execution plan.

groupConcat
Link copied to clipboard
open fun <T : String?> groupConcat(expr: GroupConcat<T>, queryBuilder: QueryBuilder)

SQL function that concatenates strings from a group into a single string.

hour
Link copied to clipboard
open fun <T> hour(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the hour field from a given date. The returned value is a number between 0 and 23 both inclusive.

insert
Link copied to clipboard
open fun insert(ignore: Boolean, table: Table, columns: List<Column<*>>, expr: String, transaction: Transaction): String

Returns the SQL command that inserts a new row into a table.

insertValue
Link copied to clipboard
open fun insertValue(columnName: String, queryBuilder: QueryBuilder)

Appends to a queryBuilder the SQL syntax for a column that represents the same values from the INSERT clause of an upsert command, which should be used in the UPDATE clause.

jsonContains
Link copied to clipboard
open fun jsonContains(target: Expression<*>, candidate: Expression<*>, path: String?, jsonType: IColumnType<*>, queryBuilder: QueryBuilder)

SQL function that checks whether a candidate expression is contained within a JSON target.

jsonExists
Link copied to clipboard
open fun jsonExists(expression: Expression<*>, vararg path: String, optional: String?, jsonType: IColumnType<*>, queryBuilder: QueryBuilder)

SQL function that checks whether data exists within a JSON expression at the specified path.

jsonExtract
Link copied to clipboard
open fun <T> jsonExtract(expression: Expression<T>, vararg path: String, toScalar: Boolean, jsonType: IColumnType<*>, queryBuilder: QueryBuilder)

SQL function that extracts data from a JSON object at the specified path, either as a JSON representation or as a scalar value.

locate
Link copied to clipboard
open fun <T : String?> locate(queryBuilder: QueryBuilder, expr: Expression<T>, substring: String)

SQL function that returns the index of the first occurrence of the given substring substring in the string expression expr

match
Link copied to clipboard
open fun <T : String?> Expression<T>.match(pattern: String, mode: FunctionProvider.MatchMode? = null): Op<Boolean>

SQL function that checks whether the given string expression matches the given pattern.

merge
Link copied to clipboard
open fun merge(dest: Table, source: Table, transaction: Transaction, clauses: List<MergeStatement.Clause>, on: Op<Boolean>?): String

Generates the SQL MERGE command which synchronizes two datasets by inserting new rows, or updating/deleting existing ones in the target table based on data from another table.

mergeSelect
Link copied to clipboard
open fun mergeSelect(dest: Table, source: QueryAlias, transaction: Transaction, clauses: List<MergeStatement.Clause>, on: Op<Boolean>, prepared: Boolean): String

Generates the SQL MERGE command which synchronizes two datasets by inserting new rows, or updating/deleting existing ones in the target table based on data from subquery.

minute
Link copied to clipboard
open fun <T> minute(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the minute field from a given date. The returned value is a number between 0 and 59 both inclusive.

month
Link copied to clipboard
open fun <T> month(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the month field from a given date. The returned value is a number between 1 and 12 both inclusive.

nextVal
Link copied to clipboard
open fun nextVal(seq: Sequence, builder: QueryBuilder)

SQL function that returns the next value of the specified sequence.

queryLimit
Link copied to clipboard
open fun queryLimit(size: Int, offset: Long, alreadyOrdered: Boolean): String
queryLimitAndOffset
Link copied to clipboard
open fun queryLimitAndOffset(size: Int?, offset: Long, alreadyOrdered: Boolean): String

Returns the SQL command that limits and offsets the result of a query.

random
Link copied to clipboard
open fun random(seed: Int?): String

SQL function that generates a random value uniformly distributed between 0 (inclusive) and 1 (exclusive).

regexp
Link copied to clipboard
open fun <T : String?> regexp(expr1: Expression<T>, pattern: Expression<String>, caseSensitive: Boolean, queryBuilder: QueryBuilder)

SQL function that performs a pattern match of a given string expression against a given pattern.

replace
Link copied to clipboard
open fun replace(table: Table, columns: List<Column<*>>, expression: String, transaction: Transaction, prepared: Boolean = true): String

Returns the SQL command that either inserts a new row into a table, or, if insertion would violate a unique constraint, first deletes the existing row before inserting a new row.

returning
Link copied to clipboard
open fun returning(mainSql: String, returning: List<Expression<*>>, transaction: Transaction): String

Returns the SQL command that performs an insert, update, or delete, and also returns data from any modified rows.

second
Link copied to clipboard
open fun <T> second(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the second field from a given date. The returned value is a number between 0 and 59 both inclusive.

stdDevPop
Link copied to clipboard
open fun <T> stdDevPop(expression: Expression<T>, queryBuilder: QueryBuilder)

SQL function that returns the population standard deviation of the non-null input values, or `null` if there are no non-null values.

stdDevSamp
Link copied to clipboard
open fun <T> stdDevSamp(expression: Expression<T>, queryBuilder: QueryBuilder)

SQL function that returns the sample standard deviation of the non-null input values, or `null` if there are no non-null values.

substring
Link copied to clipboard
open fun <T : String?> substring(expr: Expression<T>, start: Expression<Int>, length: Expression<Int>, builder: QueryBuilder, prefix: String = "SUBSTRING")

SQL function that extracts a substring from the specified string expression.

time
Link copied to clipboard
open fun <T> time(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the time field from a given temporal expression.

update
Link copied to clipboard
open fun update(targets: Join, columnsAndValues: List<Pair<Column<*>, Any?>>, limit: Int?, where: Op<Boolean>?, transaction: Transaction): String

Returns the SQL command that updates one or more rows of a join.

open fun update(target: Table, columnsAndValues: List<Pair<Column<*>, Any?>>, limit: Int?, where: Op<Boolean>?, transaction: Transaction): String

Returns the SQL command that updates one or more rows of a table.

upsert
Link copied to clipboard
open fun upsert(table: Table, data: List<Pair<Column<*>, Any?>>, expression: String, onUpdate: List<Pair<Column<*>, Any?>>, keyColumns: List<Column<*>>, where: Op<Boolean>?, transaction: Transaction): String

Returns the SQL command that either inserts a new row into a table, or updates the existing row if insertion would violate a unique constraint.

varPop
Link copied to clipboard
open fun <T> varPop(expression: Expression<T>, queryBuilder: QueryBuilder)

SQL function that returns the population variance of the non-null input values (square of the population standard deviation), or `null` if there are no non-null values.

varSamp
Link copied to clipboard
open fun <T> varSamp(expression: Expression<T>, queryBuilder: QueryBuilder)

SQL function that returns the sample variance of the non-null input values (square of the sample standard deviation), or `null` if there are no non-null values.

year
Link copied to clipboard
open fun <T> year(expr: Expression<T>, queryBuilder: QueryBuilder)

SQL function that extracts the year field from a given date.