SQL Functions
Last modified: 03 March 2025Exposed provides basic support for classic SQL functions. This topic consists of definitions for those functions, and their usage examples. It also explains how to define custom functions.
For the function examples below, consider the following table:
package org.example.tables
import org.jetbrains.exposed.sql.Table
const val MAX_TITLE_LENGTH = 150
const val MAX_REGION_LENGTH = 50
const val REVENUE_PRECISION = 12
const val REVENUE_SCALE = 2
object FilmBoxOfficeTable : Table() {
val title = varchar("title", MAX_TITLE_LENGTH)
val region = varchar("region", MAX_REGION_LENGTH)
val revenue = decimal("revenue", REVENUE_PRECISION, REVENUE_SCALE)
val month = integer("month")
val year = integer("year")
}How to use functions
To retrieve the result of an SQL function result from a query using .select(), declare the function as a variable first:
val lowerCaseTitle = FilmBoxOfficeTable.title.lowerCase()
val lowerCaseTitles = FilmBoxOfficeTable.select(lowerCaseTitle).map { it[lowerCaseTitle] }You can alias this function in the same way you alias a table or query:
val upperCaseRegion = FilmBoxOfficeTable.region.upperCase().alias("reg_all_caps")
val upperCaseRegions = FilmBoxOfficeTable.select(upperCaseRegion).map { it[upperCaseRegion] }SQL functions can be chained and combined as needed. The example below generates SQL that concatenates the string values stored in two columns, before wrapping the function in TRIM() and LOWER():
val fullFilmTitle = Concat(separator = " ", FilmBoxOfficeTable.region, stringLiteral("||"), FilmBoxOfficeTable.title)
.trim()
.lowerCase()
val fullFilmTitles = FilmBoxOfficeTable.select(fullFilmTitle).map { it[fullFilmTitle] }String functions
Lower case and upper case
To convert a string expression to lower-case or upper-case, use the .lowerCase() and .upperCase() functions respectively.
val lowerCaseTitle = FilmBoxOfficeTable.title.lowerCase()
val lowerCaseTitles = FilmBoxOfficeTable.select(lowerCaseTitle).map { it[lowerCaseTitle] }Substring
The .substring() function returns a substring value from the specified start and with the specified length.
val shortenedTitle = FilmBoxOfficeTable.title.substring(start = 1, length = 3)
val shortenedTitles = FilmBoxOfficeTable.select(shortenedTitle).map { it[shortenedTitle] }Concatenate
The concat() function returns a string value that concatenates the text representations of all non-null input values, separated by an optional separator.
val filmTitle = concat(
separator = " - ",
expr = listOf(stringLiteral("Title"), FilmBoxOfficeTable.title)
)
val filmTitles = FilmBoxOfficeTable.select(filmTitle).map { it[filmTitle] }note
This
concat()requires import statementimport org.jetbrains.exposed.sql.SqlExpressionBuilder.concat.
Locate
The .locate() function returns the index of the first occurrence of a specified substring, or 0 if the substring is not found.
val firstXSIndex = FilmBoxOfficeTable.title.locate("XS")
val firstXSIndices = FilmBoxOfficeTable.select(firstXSIndex).map { it[firstXSIndex] }Character length
The .charLength() function returns the length, measured in characters, or null if the String value is null.
val titleLength = FilmBoxOfficeTable.title.charLength()
val titleLengths = FilmBoxOfficeTable.select(titleLength).map { it[titleLength] }Aggregate functions
These functions should most likely be used in queries with .groupBy().
Min/Max/Average
To get the minimum, maximum, and average values, use the .min() .max() and .avg() functions respectively. These functions can be applied to any comparable expression:
val minRevenue = FilmBoxOfficeTable.revenue.min()
val maxRevenue = FilmBoxOfficeTable.revenue.max()
val averageRevenue = FilmBoxOfficeTable.revenue.avg()
val revenueStats = FilmBoxOfficeTable
.select(minRevenue, maxRevenue, averageRevenue, FilmBoxOfficeTable.region)
.groupBy(FilmBoxOfficeTable.region)
.map {
Triple(it[minRevenue], it[maxRevenue], it[averageRevenue])
}Sum/Count
You can use SQL functions like SUM() and COUNT() directly with a column expression:
val revenueSum = FilmBoxOfficeTable.revenue.sum()
val revenueCount = FilmBoxOfficeTable.revenue.count()
val revenueReport = FilmBoxOfficeTable
.select(revenueSum, revenueCount, FilmBoxOfficeTable.region)
.groupBy(FilmBoxOfficeTable.region)
.map {
it[revenueSum] to it[revenueCount]
}Statistics
Some databases provide aggregate functions specifically for statistics and Exposed provides support for four of these: .stdDevPop(), .stdDevSamp(), .varPop(), .varSamp(). The following example retrieves the population standard deviation of values stored in the revenue column:
val revenueStdDev = FilmBoxOfficeTable.revenue.stdDevPop()
val stdDev = FilmBoxOfficeTable
.select(revenueStdDev)
.singleOrNull()
?.get(revenueStdDev)Custom functions
If you can't find your most loved function used in your database (as Exposed provides only basic support for classic SQL functions), you can define your own functions.
There are multiple options to define custom functions:
Functions without parameters
.function() simply wraps the column expression in parentheses with the string argument as the function name:
val sqrtRevenue = FilmBoxOfficeTable.revenue.function("SQRT")
// generates SQL: SQRT(SALES.REVENUE)
val sqrt = FilmBoxOfficeTable
.select(sqrtRevenue)
.singleOrNull()
?.get(sqrtRevenue)Functions with additional parameters
The CustomFunction class accepts a function name as the first argument and the column type that should be used to handle its results as the second. After that, you can provide any amount of additional parameters separated by a comma:
val replacedTitle = CustomFunction(
functionName = "REPLACE",
columnType = TextColumnType(),
FilmBoxOfficeTable.title, stringLiteral("Title"), stringLiteral("New Title")
)
// generates SQL: REPLACE(FILMBOXOFFICE.TITLE, 'Title', 'New Title')
val replacedTitles = FilmBoxOfficeTable.select(replacedTitle).map { it[replacedTitle] }There are also shortcuts for String, Long, and DateTime functions:
Using one of these shortcuts, the example above could be simplified to:
val replacedStringTitle = CustomStringFunction(
"REPLACE", FilmBoxOfficeTable.title, stringLiteral("Title"), stringLiteral("New Title")
)In the following example, CustomDateFunction is used in an H2 database to mimic its DATEADD() function in order to calculate a date three months before the current one. In is then chained with Exposed's built-in .month() function to return the month of the date found, so it can be used in a query:
val threeMonthsAgo = CustomDateFunction(
functionName = "DATEADD",
stringLiteral("MONTH"),
intLiteral(-3),
CurrentDate
).month()
// generates SQL: MONTH(DATEADD('MONTH', -3, CURRENT_DATE))
val filmsInLast3Months = FilmBoxOfficeTable
.selectAll()
.where { FilmBoxOfficeTable.month greater threeMonthsAgo }
.map { it[FilmBoxOfficeTable.title] }Functions that require more complex query building
All functions in Exposed extend the abstract class Function, which takes a column type and allows overriding toQueryBuilder(). This is what CustomFunction actually does, which can be leveraged to create more complex queries.
For example, Exposed provides a .trim() function that removes leading and trailing whitespace from a String. In some databases (like H2 and MySQL), this is just the default behavior as specifiers can be provided to limit the trim to either leading or trailing. These databases also allow you to provide a specific substring other than spaces to remove. The custom function below supports this extended behavior:
package org.example.examples
import org.jetbrains.exposed.sql.Expression
import org.jetbrains.exposed.sql.Function
import org.jetbrains.exposed.sql.QueryBuilder
import org.jetbrains.exposed.sql.TextColumnType
import org.jetbrains.exposed.sql.append
enum class TrimSpecifier { BOTH, LEADING, TRAILING }
class CustomTrim<T : String?>(
val expression: Expression<T>,
val toRemove: Expression<T>?,
val trimSpecifier: TrimSpecifier
) : Function<String>(TextColumnType()) {
override fun toQueryBuilder(queryBuilder: QueryBuilder) {
queryBuilder {
append("TRIM(")
append(trimSpecifier.name)
toRemove?.let { +" $it" }
append(" FROM ", expression, ")")
}
}
}
fun <T : String?> Expression<T>.customTrim(
toRemove: Expression<T>? = null,
specifier: TrimSpecifier = TrimSpecifier.BOTH
): CustomTrim<T> = CustomTrim(this, toRemove, specifier)note
Ensure that the correct import statement is used:
import org.jetbrains.exposed.sql.Function. OtherwiseFunctionfromkotlin-stdlibmay be resolved instead and cause compilation errors.
This custom function can then be used to achieve the exact trim that is needed:
FilmBoxOfficeTable.insert {
it[title] = "Star Wars: The Phantom Menace - Episode I"
it[region] = "Spain"
it[revenue] = 99.toBigDecimal()
it[month] = REVENUE_MONTH
it[year] = REVENUE_YEAR
}
val leadingStarWarsTrim = FilmBoxOfficeTable.title.customTrim(stringLiteral("Star Wars:"), TrimSpecifier.LEADING)
val titleWithoutPrefix = FilmBoxOfficeTable.select(leadingStarWarsTrim).single()[leadingStarWarsTrim] // The Phantom Menace - Episode I
val trailingEpisodeTrim = FilmBoxOfficeTable.title.customTrim(stringLiteral("- Episode I"), TrimSpecifier.TRAILING)
val titleWithoutSuffix = FilmBoxOfficeTable.select(trailingEpisodeTrim).single()[trailingEpisodeTrim] // Star Wars: The Phantom MenaceWindow functions
Window functions allow calculations across a set of table rows that are related to the current row.
Existing aggregate functions (like sum(), avg()) can be used, as well as new rank and value functions:
To use a window function, include the OVER clause by chaining .over() after the function call. A PARTITION BY and ORDER BY clause can be optionally chained using .partitionBy() and .orderBy(), taking multiple arguments:
val window1 = FilmBoxOfficeTable.revenue.sum()
.over()
.partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
.orderBy(FilmBoxOfficeTable.revenue)
val result1 = FilmBoxOfficeTable.select(window1).map { it[window1] }
val window2 = rowNumber()
.over()
.partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
.orderBy(FilmBoxOfficeTable.revenue)
val result2 = FilmBoxOfficeTable.select(window2).map { it[window2] }
val window3 = FilmBoxOfficeTable.revenue.sum()
.over()
.orderBy(FilmBoxOfficeTable.year to SortOrder.DESC, FilmBoxOfficeTable.title to SortOrder.ASC_NULLS_FIRST)
val result3 = FilmBoxOfficeTable.select(window3).map { it[window3] }Frame clause functions, such as rows(), range(), and groups(), are also supported and take a WindowFrameBound option depending on the expected result:
val window4 = FilmBoxOfficeTable.revenue.sum()
.over()
.partitionBy(FilmBoxOfficeTable.year, FilmBoxOfficeTable.title)
.orderBy(FilmBoxOfficeTable.revenue)
.range(WindowFrameBound.offsetPreceding(2), WindowFrameBound.currentRow())
val result4 = FilmBoxOfficeTable.select(window4).map { it[window4] }note
If multiple frame clause functions are chained together, only the last one will be used.