documentation-website/Writerside/topics/SQL-Functions.md
Exposed 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:
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/tables/FilmBoxOfficeTable.kt"/>
To retrieve the result of an SQL function result from a query using .select(), declare the function as a variable first:
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="35-36"/>
You can alias this function in the same way you alias a table or query:
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="39-40"/>
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():
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="43-46"/>
To convert a string expression to lower-case or upper-case, use the .lowerCase()
and
.upperCase()
functions respectively.
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="35-36"/>
The .substring()
function returns a substring value from the specified start and with the specified length.
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="49-50"/>
The concat()
function returns a string value that concatenates the text representations of all non-null input values, separated by an optional separator.
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/StringFuncExamples.kt" include-lines="53-57"/>
The .locate()
function returns the index of the first occurrence of a specified substring, or 0 if the substring is not found.
The .charLength()
function returns the length, measured in characters, or null if the String value is null.
These functions should most likely be used in queries with .groupBy().
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:
You can use SQL functions like SUM() and COUNT() directly with a column expression:
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:
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:
.function() simply wraps the column expression
in parentheses with the string argument as the function name:
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:
There are also shortcuts for String, Long, and DateTime functions:
Using one of these shortcuts, the example above could be simplified to:
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/CustomFuncExamples.kt" include-lines="46-48"/>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:
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:
This custom function can then be used to achieve the exact trim that is needed:
<code-block lang="kotlin" src="exposed-sql-functions/src/main/kotlin/org/example/examples/CustomFuncExamples.kt" include-lines="72-81,83-85"/>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:
cumeDist()denseRank()firstValue()lag()lastValue()lead()nthValue()nTile()percentRank()rank()rowNumber()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:
Frame clause functions, such as rows(),
range(),
and groups(),
are also supported and take a WindowFrameBound
option depending on the expected result: