# SQL function reference¶

You can use supported functions in SQL query statements.

## Functions for SQL¶

With functions you can transform, calculate, combine or merge the values of other columns from the current table. On top of that, functions can refer to each other.

The functions supported in SQL are roughly the same as the set of functions supported by formulas in SeaTable.

The basic syntax of functions is as follows:

``````FunctionName(parameters...)
``````

The parameters can be number,string,constants,column name or other functions. Column name cannot be an alias. If the column name contains "-", you can use "`" to enclose it.

Currently SQL query offers the following functions:

• Operands
• Mathematical functions
• Text functions
• Date functions
• Geo functions
• Logical functions
• Statistical functions

In this article, we will show you a complete overview of all functions with examples. If you are looking for a specific function, you can use the Ctrl+F to quickly find an entry on this page.

## Functions with examples¶

You can use the following constants in the function:

OPERATOR DESCRIPTION INPUT RESULT
e Returns the Euler number e=2.71828... e+1 3.71828183
pi Returns the circle number Pi. pi 3.14159265
true() Returns the logical value 'true'. true() true
false() Returns the logical value 'false'. false() false

### Operands¶

Parameters must be strings or numbers. If a number is passed to a parameter that expects a string, it'll be converted to string, and vice versa.

OPERATOR DESCRIPTION INPUT RESULT
subtract(num1,num2) Subtracts one numeric value (num2) from another (num1). subtract(5,4) 1
multiply(num1,num2) Multiplies two numeric values. multiply(3,4) 12
divide(num1,num2) Divides one numeric value (num1) by another (num2). divide(3,2) 1.5
mod(num1,num2) Calculates the remainder of a division. mod(15,7) 1
power(num1,num2) Calculates the power (num2) of a number (num1). power(3,2) 9
greater(num1,num2) Checks if a numeric value (num1) is greater than another (num2) and returns the logical value 'true' or 'false'. greater(2,3) false
lessthan(num1,num2) Checks if a numeric value (num1) is less than another (num2) and returns the logical value 'true' or 'false'. lessthan(2,3) true
greatereq(num1,num2) Checks whether a numeric value (num1) is greater than or equal to another (num2) and returns the logical value 'true' or 'false'. greatereq(2,3) false
lessthaneq(num1,num2) Checks whether a numeric value (num1) is less than or equal to another (num2) and returns the logical value 'true' or 'false'. lessthaneq(2,3) false
equal(num1,num2) Checks if two values (number1, number 2) are equal and returns the logical value 'true' or 'false'. equal(`Old price`, `New price`) false
unequal Checks whether two values (number1, number2) are not equal and returns the logical value 'true' or 'false'. unequal(`Old price`, `New price`) true
concatenate(string1, string2, ...) Combines several character strings (string1, string 2, ...) into one character string. concatenate(`Supplier`, " has the product ", `Product`) Microsoft has the product Windows

### Mathematical functions¶

Parameters must be numbers. If string is passed to a parameter, it'll be converted to number.

OPERATOR DESCRIPTION INPUT RESULT
abs(number) Returns the absolute value of a number. abs(-2) 2
ceiling(number, significance) Rounds a number to the nearest integer or to the nearest multiple of the specified significance. If either argument is non-numeric, the formula returns an empty value. ceiling(2.14) 3
If the number is an exact multiple of the significance, then no rounding occurs. If the number and the significance are negative, then the rounding is away from 0. If the number is negative and the significance is positive, then the rounding is towards 0. ceiling(-2.14, 4) 0
even(number) Assigns a real number to the nearest larger even number. even(2.14) 4
exp(number) Exponential function for Euler's number e. Returns the value of e given high (number). expr(1) 2.71828...
floor(number, significance) Rounds a number to the nearest integer or to the nearest multiple of the specified significance. If either argument is non-numeric, the formula returns an empty value. floor(2.86) 2
If the number is an exact multiple of the significance, then no rounding takes place. If the sign of the number is positive, then the rounding is towards 0. If the sign of the number is negative, then the rounding is away from 0. floor(-3.14, 5) -5
int(number) Assigns the nearest smaller integer to a real number. int(-3.14) -4
lg(number) Logarithm function (number) with 10 as base. lg(100) 2
log(number, base) Logarithm function (number) with definable base. log(81, 3) 4
But if no base is given, this function works exactly like lg(), with 10 as base. log(1000) 3
odd(number) Assigns a real number to the nearest larger odd number. odd(-2.14) -1
round(number, digits) Rounds a number to the nearest integer. If no decimal place (digits) is specified, the number is rounded to the 1st digit to the left of the decimal point. round(3.14) 3
If a positive decimal place (digits) is given, the digit to the right of the decimal point is rounded. round(3.14, 1) 3.1
If a negative decimal place (digits) is given, is rounded to the left of the decimal point. round(3.14, -3) 0
rounddown(number, digits) Rounds a number towards zero. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point. rounddown(3.12, 1) 3.1
roundup(number, digits) Rounds a number from zero to the nearest whole number. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point. roundup(-3.15) -4
sign(number) Checks whether a number is greater, equal or less than 0. Returns the values 1, 0 and -1 respectively. In other words: it returns the sign of a number, for '+', 'zero' and '-' with 1, 0, and -1 respectively. sign(-2) -1
sqrt(number) Returns the square root of a number. sqrt(81) 9

### Text functions¶

OPERATOR DESCRIPTION INPUT RESULT
exact(string1, string2) Checks whether two character strings (string1, string2) are exactly identical. Returns the values 'true' or 'false' respectively. Case sensitive. exact('SeaTable', 'Seatable') false
find(findString, sourceString, startPosition) Returns the start position of a string (findString) within another string (sourceString). It is case sensitive. Without find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value ('') is returned. find('Sea', 'seaTable', 1) 0
The search will start from the given 'startPosition'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since nothing was found from this position. find('table', 'big table', 4) 5
left(string, count) Returns the specified number (count) of characters at the beginning of a string. left('SeaTable', 3) Sea
len(string) Returns the number of characters in a string. len('SeaTable') 8
lower(string) Converts a character string to lower case letters. lower('German) german
mid(string, startPosition, count) Returns the specified number (count) of characters from the specified start position (startPosition) of a string. mid('SeaTable is the best', 1, 8) SeaTable
Start position (startPosition) and count must not be empty, negative or zero. However, if start position (startPosition) and number (count) are given as decimal, they are rounded down. Too much count is ignored. mid('SeaTable is the best.', 10.9, 27.3) is the best.
replace(sourceString, startPosition, count, newString) Replaces a part (count) of a character string (sourceString) from a certain start position (startPosition) with another character string (newString). The number (count) of characters is only taken into account for the old string (sourceString), but not for the new string (newString). replace('SeaTable is the best.', 1, 8, 'Seafile') Seafile is the best.
If number (count) is given as zero, the new string (newString) is simply added to the old string (sourceString) from the start position (startPosition). replace('SeaTable is the best.', 1, 0, 'Seafile') SeafileSeaTable is the best.
rept(string, number) Repeats a string as often (number) as specified. rept('Sea ', 3) SeaSeaSea
right(string, count) Returns the specified number (count) of characters at the end of a string. right('SeaTable', 5) Table
search(findString, sourceString, startPosition) Returns the start position of a string (findString) within another string (sourceString). It is not case-sensitive. Without find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value ('') is returned. search('Sea', 'seaTable', 1) 1
The search will start from the given 'startPosition'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since nothing was found from this position. search('table', 'big table', 6) 0
substitute(sourceString, oldString, newString, index) Replaces existing text (oldString) with new text (newString) in a string (sourceString). If there is more than one text (oldString) in the string (sourceString), only the 'index'-th text is replaced. The text is case-sensitive. substitute('SeaTableTable', 'Table', 'file', 1) SeafileTable
If the index is given as 0 or not, all found text (oldString) will be replaced by the new text (newString). substitute('SeaTableTable', 'Table', 'file') Seafilefile
T(value) Checks whether a value is text. If so, the text is returned. If no, the return value is empty. T('123') 123
text(number, format) Converts a number into text and formats it in the specified format. The format can be percent and number as well as dollar, euro and yuan. text(150, 'euro') €150
When a number is converted directly to percent, its absolute value is retained. In other words, 50 is converted into 5000%. But if you want 50%, you have to divide the number by 100 before the conversion. text(50, 'percent') 5000%
trim(string) Removes spaces at the beginning and end of a string. trim(' SeaTable ') SeaTable
upper(string) Converts a string to uppercase letters. upper('German) GERMAN
value(string) Converts a text (string) representing a number into a number. value('123') 123

### Date functions¶

When passing a parameter with time or date type, you can specify a contant in "2021-09-01 12:00:01" or "2021-09-01" format. When you query the result of a date function in SQL, the result will be converted to a string in RFC3339 format, e.g. "2021-09-03T00:00:00+02:00". Please note that if a date function returns a date type, it cannot be used as parameter for text or maths functions.

OPERATOR DESCRIPTION INPUT RESULT
date(year, month, day) Returns a date in international format (ISO) from entered year, month and day. If the year is entered with two digits, it is automatically understood as a year in the 1900s. If the number of the month or day is too large (greater than 12 or 31 respectively), these months or days are automatically converted to the next year or month. date(2021, 1, 3) 2021-01-03T00:00:00+02:00
dateAdd(date, count, unit) Adds the specified number (count) of years ('years'), months ('months'), weeks ('weeks'), days ('days'), hours ('hours'), minutes ('minutes') or seconds ('seconds') to a date/time ('date'). dateAdd('2020-02-03', 2, 'days') 2020-02-05T00:00:00+02:00
Tip: if you want to add a complex duration (count) such as 1 day 12 hours, you can convert it to e.g. 24+12=36 hours ('hours') and enter it into the formula as a uniform duration (count). The duration is converted to the smallest unit: in this case, hours. dateAdd('2020-09-04 13:05:18', 36, 'hours') ODER dateAdd(`form submission`, 36, 'hours') 2020-09-06T01:05:18+02:00
datedif(startDate, endDate, unit) Calculates the seconds, days, months, or years between two date values. The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). If the startDate is empty, a default value of "1900-01-01" will be set. If both date values are empty, it will return 0. dateDif('2018-01-01', '2020-01-01') 2
The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). dateDif('2019-10-11', '2020-12-12', 'M') 14
day(date) Returns the day of a date as a number. The returned number is between 1 and 31. day('2020-01-03) 3
eomonth(startDate, months) Determines the date of the last day of the month that is the specified number (months) of months after the specified date (startDate). If the number (months) is given as 0, the last day of the month is simply determined. eomonth('2020-01-01', 1) 2020-02-29T00:00:00+02:00
If the number (months) is given as negative, the date of the last day of the month that contains the absolute number (months) of months before the specified date (startDate) is determined. eomonth('2020-01-01', -1) 2019-12-31T00:00:00+02:00
hour(date) Returns the hour of a date as a number. The number returned is between 0 and 23. hour('2020-02-14 13:14:52) 13
If no hour is contained in the time specification (date), 0 is returned. hour('2020-02-14) 0
hours(startDate, endDate) Returns the number of hours between two date values (startDate and endDate). The minutes in the date values are not taken into account. hours('2020-02-14 13:14', '2020-02-14 15:14') 2
If no hours are included in the time specification (startDate or endDate), 0 o'clock on this day is automatically assumed. hours('2020-02-14', '2020-02-14 15:14') 15
minute(date) Returns the minutes of a time specification (date) as a number. The number returned is between 0 and 59. minute('2020-02-14 13:14:52 14
If no minutes are included in the time (date), 0 is returned. minute('2020-02-14) 0
month(date) Returns the month of a date as a number. The returned number is between 1 (January) and 12 (December). month('2020-02-14 13:14:52) 2
months(startDate, endDate) Returns the number of months between two date values (startDate and endDate). The days and time in the date values are not taken into account. months('2020-02-01 13:14', '2020-03-31 15:54') 1
If no month is given in the date values (startDate, endDate), January is automatically assumed to be the month. months('2020', '2021') 12
networkdays(startDate, endDate, holiday1, holiday2, ...) Returns the number of full working days between two dates (startDate and endDate). You can also define holidays other than Saturday and Sunday (holiday1, holiday2, etc.), which are also deducted. If you do not want to include public holidays, you can simply omit these parameters. networkdays('2020-01-01', '2020-01-07','2020-01-01') 4
Please note that the specified last day (endDate) is also included in the formula. That means, as in this formula, three working days are counted: the 7th, 8th and 9th of September, 2020. networkdays('2020-09-07', '2020-09-09') 3
now() Returns the current date and time. This column is only updated automatically when the Base is reloaded. now() 2020-09-07T12:59+02:00
second(date) Returns the seconds of a time (date) as a number. The number returned is between 0 and 59. second('2020-02-14 13:14:52') 52
today() Returns the current date. This column is only updated automatically if the Base has been reloaded. today() 2020-09-07T00:00:00+02:00
This function is handy for calculating time between a certain date & time and now. On each reload or recalculation of the Base, the calculation is updated. networkdays('2020-09-01', today()) 4
weekday(date, weekStart) Returns the weekday of a date as a number. The returned number between 1 and 7, where you can define the first day of the week (weekStart): Monday ('Monday') or Sunday ('Sunday' or omitted, since the start as Sunday is the default). A third option is not possible. Upper/lower case is not considered. weekday('2020-01-01', 'Monday') 3
If no 'weekStart' is given or if a 'weekStart' other than 'Monday' or 'Sunday' is given, it is always assumed to be 'Sunday'. So if it should be 'Monday', enter 'Monday'; if it should be 'Sunday', you can omit this parameter. weekday('2020-01-01', 'Thursday') OR weekday('2020-01-01') 4
weeknum(date, return_type) Returns the absolute week number of a date as a number. The returned number is between 1 and 53, where you can define the first day of the week (return_type). Enter the number 1 or 2, or 11 to 17, and 21 as "return_type" to define the start of a week: 1/Sunday、2/Monday、11/Monday、12/Tuesday、13/Wednesday、14/Thursday、15/Friday、16/Saturday、17/Sunday. If you want the week number to be returned according to ISO standard, specify the number of 21 as "return_type", or use the function isoweeknum. weeknum('2020-01-12', 11) 2
If no 'return_type' is given, it is always assumed to be 'Sunday'. weeknum('2020-01-12') 3
year(date) Returns the year of a date as a number. year('2020-01-01') 2020
startofweek(date, weekStart) Returns the first day of the week in which the date is located. WeekStart defaults to sunday, or it can be set to monday. startofweek('2021-04-28') 2021-4-25T00:00:00+02:00
quarter(date) Returns the quarter of the date, the return value is 1, 2, 3, 4. quarter('2021-01-01') 1
isodate(date) Returns the ISO string representation of the date. isodate('2021-01-01 11:00:00') 2021-01-01
isomonth(date) Returns the ISO string representation of the year and month isomonth('2021-01-01 11:00:00') 2021-01

### Geo functions¶

Geo functions are available since version 2.7.0

OPERATOR DESCRIPTION INPUT RESULT
province(geolocation) Returns the province of a Geolocation column. province(column_name) 北京市
city(geolocation) Returns the city of a Geolocation column. city(column_name) 北京市
district(geolocation) Returns the district of a Geolocation column. district(column_name) 朝阳区

### Logical functions¶

OPERATOR DESCRIPTION INPUT RESULT
and(logical1, logical2, ...) Checks if all arguments (logical1, logical2, ...) are true (valid, not empty and not equal to zero). If yes, 'true' is returned, otherwise 'false'. and(1, '', 2) false
if(logical, value1, value2) Checks if an argument (logical) is true and if yes, returns the first value (value1) and if no, returns the second value (value2). if(1>2, 3, 4) 4
For the condition (logical) only a comparison with is allowed. If you enter only condition (logical) and the first value (value1): it will return the first value (value1) if the condition (logical) is true; and it will return an empty value ('') if the condition (logical) is false. if(`Budget`>`Price`, 'Yes') Yes
ifs(logical1, value1, logical2, value2, ...) Checks if one or more conditions (logical1, logical2, ...) are true and returns a value (value1, value2, ...) that matches the first TRUE condition. ifs( 1>2, 3, 5>4, 9) 9
not(boolean) Inverts the logical value (boolean). In other words: converts 'true' to 'false' and 'false' to 'true'. not(and(1, '', 2)) true
or(logical1, logical2, ...) Checks if at least 1 of the arguments (value1, value2, ...) is true (valid, not empty and not equal to zero), and returns 'true' in this case. If all arguments are false, then returns 'false'. or(1,'',2) true
switch(logical, matcher1, value1, matcher2, value2, ..., default) Evaluates an expression (logical) against a list of values (matcher) and returns the result (value) corresponding to the first matching value. If there is no match, an optional default value is returned. At least 3 parameters (logical, matcher, value) must be specified. switch(`grades`, 1, 'very good', 2, 'good', 3, 'satisfactory', 4, 'passed', 'failed') very good
If there are several identical values in the value list (matcher), only the first hit is taken into account. switch(int(68/10), 6, 'OK', 6, 'KO') OK
xor(logical1, logical2, ...) Returns the contravalence of all arguments. In other words, checks if the number of true arguments is (logical) odd and returns 'true'. xor(1, 0, 2\<1) false

### Statistical functions¶

OPERATOR DESCRIPTION INPUT RESULT
average(number1, number2, ...) Returns the average of the numbers (number1, number2, ...) average(1, 2, 3, 4, 5) 3
counta(textORnumber1, textORnumber2, ...) Counts the number of non-empty cells (textORnumber1, textORnumber2, ...). These cells can be text or numbers. In this example, 1 and 2 are numbers, '3' is text, and '' is an empty value. counta(1, '', 2, '3') 3
countall(textORnumber1, textORnumber2, ...) Counts the number of elements (textORnumber1, textORnumber2, ...) including numbers (1, 2), text ('3') and empty cells (''). countall(1, '', 2, '3') 4
countblank(textORnumber1, textORnumber2, ...) Counts the number of empty cells. countall(1, '', 2, '3') 1
countItems(column) Counts the number of items in a column. The supported column types are multiple select, collaborator, file, image. (available since version 2.7.0) countItems(column_name) 2