The included set of the standard functions is implemented in many DBMS, but only small part of them is implemented in SQLite. The mechanism of User-Defined Functions can be used for solving this task. You can define your own implementation of necessary function with the help of this mechanism.
dotConnect for SQLite gives a set of the abstract classes (SQLiteFunction and its descendants). and method for their registration in DBMS - RegisterFunction method in the SQLiteConnection class.
These functions are described in the table below:
To simplify work with SQLite, we have defined the set of the standard SQL functions in the dotConnect for SQLite.
The tables below contain an information about implemented standard functions that were grouped by their purposes:
- Aggregate Functions
- Mathematical Functions
- String Functions
- Datetime Functions
- Bitwise Functions
- Other Functions
Aggregate Functions
Function | Definition | Example | Remarks |
---|---|---|---|
VAR(expression) | Returns the statistical variance of all values in the specified expression. |
The following example returns the variance for all Deptno values in the Dept table. SELECT VAR(Deptno) FROM Dept; |
If VAR is used on all items in a SELECT statement, each value in the result set
is included in the calculation. |
VARP(expression) | Returns the statistical variance for the population for all values in the specified expression. |
The following example returns the variance for the population for all Deptno values in the Dept table. SELECT VARP(Deptno) FROM Dept; |
If VARP is used on all items in a SELECT statement, each value in the result set
is included in the calculation. |
STDEV(expression) | Returns the statistical standard deviation of all values in the specified expression. |
The following example returns the standard deviation for all Deptno values in the SalesPerson Dept. SELECT STDEV(Deptno) FROM Dept; |
If STDEV is used on all items in a SELECT statement, each value in the
result set is included in the calculation. |
STDEVP(expression) | Returns the statistical standard deviation for the population for all values in the specified expression. |
The following example returns the standard deviation for the population for all Deptno values in the SalesPerson Dept. SELECT STDEVP(Deptno) FROM Dept; |
If STDEVP is used on all items in a SELECT statement, each value in the result
set is included in the calculation. |
Mathematical Functions
Function | Definition | Example | Remarks |
---|---|---|---|
FLOOR(expession) | Returns the largest integer less than or equal to the specified numeric expression. |
The following example shows positive number, negative number, and string argument with the FLOOR function SELECT FLOOR(12.9), |
For integer arguments, the return value has integer type. |
CEILING(expession) | Returns the smallest integer greater than, or equal to, the specified numeric expression. |
The following example shows positive number, negative number, and string argument with the CEILING function SELECT CEILING(12.9), |
For integer arguments, the return value has integer type. |
POWER(expression, y) | Returns the value of the specified expression to the specified power. |
The following example returns POWER results; SELECT POWER(10.5, 4); |
expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type. |
TRUNCATE(expression, digits) | Returns the value, truncated to the nearest specified digits. |
The following example returns truncated value to the one digit after decimal point; SELECT TRUNCATE(12.94, 1) |
expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type. |
SQRT(expression) | Returns the square root of the specified floating-point value |
The following example returns the square root of 16; SELECT SQRT(16); |
expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type. |
COS(expression) | Returns the trigonometric cosine of the specified angle, in radians, in the specified expression. |
The following example returns the COS of the specific angle. SELECT COS(3.14); |
expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type. |
SIN(expression) | Returns the trigonometric sine of the specified angle, in radians, in the specified expression. |
The following example returns the SIN of the specific angle. SELECT SIN(3.14); |
expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type. |
ACOS(expression) | Returns the angle, in radians, whose cosine is the specified floating-point expression; also called arccosine. |
The following example returns the ACOS of the specified number SELECT ACOS(-1); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type, with a value from -1 through 1. |
ASIN(expression) | Returns the angle, in radians, whose sine is the specified floating-point expression; also called arcsine. |
The following example returns the ASIN of the specified number SELECT ASIN(1); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type,
with a value from -1 through 1. |
ATAN(expression) | Returns the angle in radians whose tangent is a specified floating-point expression. This is also called arctangent. |
The following example returns the ATAN of the specified number SELECT ATAN(1); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
ATAN2(expression1, expression2) | Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified floating-point expressions. |
The following example calculates the ATN2 for the specified x = 1.5 and y =2.0 components SELECT ATAN(2,1.5); |
expression1, expression1 are expressions of the float type or of a type that can be implicitly converted to floating-point type. |
COT(expression) | Returns the trigonometric cotangent of the specified angle, in radians, in the specified floating-point expressions. |
The following example returns the COT for the specific angle. SELECT COT(124.1332); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
PI() | Returns the constant value of PI. |
The following example returns the value of PI: SELECT PI(); |
Return Types: floating-point type. |
DEGREES(expression) | Returns the corresponding angle in degrees for an angle specified in radians. |
The following example returns the number of degrees in an angle of PI/2 radians: SELECT DEGREES(PI()/2); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
EXP(expression) | Returns the exponential value of the specified floating-point expression. |
The following example returns the exponential value of the 10: SELECT EXP(10); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
LOG(expression) | Returns the natural logarithm of the specified floating-point expression. |
The following example calculates the LOG for the specified number 2.718: SELECT LOG(2.718); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
LOG(base,expression) | Returns the logarithm of the specified floating-point expression an arbitrary base. |
The following example calculates the base-2 LOG for the specified number 8: SELECT LOG(2, 8); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
LOG10(expression) | Returns the base-10 logarithm of the specified floating-point expression. |
The following example calculates the LOG10 of the specified value: SELECT LOG10(100); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
RADIANS(expression) | Returns radians when a numeric expression, in degrees, is entered. |
The following example returns the number of radians in an angle of 90 degrees: SELECT DEGREES(90); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
SIGN(expression) | Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. |
The following example returns the SIGN values of number -2.34: SELECT SIGN(-2.34); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type. |
TAN(expression) | Returns the tangent of the input expression. |
The following example returns the tangent of 2.5: SELECT TAN(2.5); |
expression is an expression of the type float or of a type that can be implicitly converted to floating-point type,
interpreted as number of radians. |
String Functions
Function | Definition | Example | Remarks |
---|---|---|---|
CONTAINS(substr, str) | Returns true if substr is contained in string. |
The following example returns 1: SELECT CONTAINS('bc', 'abc'); |
Returns 1 or 0 to indicate whether 'str' completely contains 'substr'. |
INDEXOF(substr, str, pos) | Returns the position of the first occurrence of substring 'substr' in string 'str', starting at position 'pos'. |
The following example returns 4: SELECT INDEXOF('xyz', 'abcxyz', 3); |
Returns 0 if 'substr' is not in 'str'. |
STARTSWITH(substr, str) | Returns 1 or 0 to indicate whether the beginning of 'str' matches the 'substr'. |
The following example returns 1: SELECT STARTSWITH('ab', 'abc'); |
Returns 0 if 'substr' is not in 'str'. |
ENDSWITH(substr, str) | Returns 1 or 0 to indicate whether the end of 'str' matches the 'substr'. |
The following example returns 1: SELECT STARTSWITH('bc', 'abc'); |
Returns 0 if 'substr' is not in 'str'. |
REVERSE(str) | Returns the string 'str' with the order of the characters reversed. |
The following example returns dcba: SELECT REVERSE('abcd'); |
Returns 0 if 'substr' is not in 'str'. |
Datetime Functions
Function | Definition | Example | Remarks |
---|---|---|---|
ADDNANOSECONDS (expression, number) | Adds the specified 'number' of nanoseconds to the 'expression'. |
The following example adding 500 nanoseconds to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDMICROSECONDS(expression, number) | Adds the specified 'number' of microseconds to the 'expression'. |
The following example adding 500 microseconds to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDMILLISECONDS (expression, number) | Adds the specified 'number' of milliseconds to the 'expression'. |
The following example adding 500 milliseconds to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDSECONDS (expression, number) | Adds the specified 'number' of seconds to the 'expression'. |
TThe following example adding -12 seconds to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDMINUTES (expression, number) | Adds the specified 'number' of minutes to the 'expression'. |
The following example adding 10 minutes to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDMINUTES (expression, number) | Adds the specified 'number' of minutes to the 'expression'. |
The following example adding 10 minutes to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDHOURS (expression, number) | Adds the specified 'number' of hours to the 'expression'. |
The following example adding -5 hours to the specified time: SELECT ADDHOURS |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDDAYS (expression, number) | Adds the specified 'number' of days to the 'expression'. |
The following example adding 5 days to the specified time: SELECT ADDDAYS |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDMONTHS (expression, number) | Adds the specified 'number' of months to the 'expression'. |
The following example adding 5 months to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
ADDYEARS (expression, number) | Adds the specified 'number' of years to the 'expression'. |
The following example adding 5 years to the specified time: SELECT |
expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value. |
CREATEDATETIME(year, month, day, hour, minute, second) | Returns a datetime value as the value calculated from the 'year', 'month', 'day', 'hour', 'minute', and 'second' arguments. |
The following example return new datetime value: SELECT CREATEDATETIME(2000,01,01, 11, 23, 52); |
year, month, day, hour, minute, second are values of integer type or values of a type that can be implicitly converted to integer type,
Returns NULL when any of arguments is NULL. |
CREATEDATETIMEOFFSET(year, month, day, hour, minute, second, tzoffset) | Returns a datetime value as the current date and time relative to the Coordinated Universal Time (UTC). |
The following example return new datetimeoffset value: SELECT CREATEDATETIME(2000,01,01, 11, 23, 52); |
year, month, day, hour, minute, second, tzoffset are values of integer type or values of a type
that can be implicitly converted to integer type,
tzoffset = between from -840 to 840 minutes (-14 to +14 hours) |
CREATETIME(year, month, day, hour, minute, second) | Returns a time value as the value calculated from the 'hour', 'minute', and 'second' arguments. |
The following example return new time value: SELECT CREATETIME(11, 23, 52); |
hour, minute, second are values of integer type or values
of a type that can be implicitly converted to integer type. |
CURRENTDATETIMEOFFSET() | Returns a datetime value as the current date and time relative to the Coordinated Universal Time (UTC). |
The following example return current datetimeoffset value: SELECT CURRENTDATETIMEOFFSET(); |
Return Value: a string representation of Time. |
DATETIMEINTERVAL(startExpression, endExpression) | Returns the date and time interval between 'startExpression' and 'endExpression'. |
The following example return time interval between two datetime values: SELECT DATETIMEINTERVAL('2000-01-01 11:23:52', '2005-06-20 12:24:54'); |
startExpression, endExpression are expressions of the string type
that represents Datetime, Time or DateTimeOffset value. |
DIFFMONTHS(startExpression, endExpression) | Returns the difference, in months, between ''startExpression' and 'endExpression'. |
The following example return number of months between two datetime values: SELECT DIFFMONTHS('2000-01-01 11:23:52', '2005-06-20 12:24:54'); |
startExpression, endExpression are expressions of the string
type that represents Datetime, Time or DateTimeOffset value. |
DIFFYEARS(startExpression, endExpression) | Returns the difference, in years, between ''startExpression' and 'endExpression'. |
The following example return number of years between two datetime values: SELECT DIFFYEARS('2000-01-01 11:23:52', '2005-06-20 12:24:54'); |
startExpression, endExpression are expressions of the string type that
represents Datetime, Time or DateTimeOffset value. |
GETTOTALOFFSETMINUTES(datetimeoffset) | Returns the number of minutes that the datetimeoffset is offset from GMT. This is generally between +840 and -840 (+ or - 14 hrs). |
The following example return offset minutes from specified datetimeoffset: SELECT GETTOTALOFFSETMINUTES( '2000-01-01 11:23:52 +05:30'); |
datetimeoffset is an expression of the string type that represents DateTimeOffset value. |
TRUNCATE(expression) | Returns the expression, with the time values truncated. |
The following example return truncated datetime: SELECT TRUNCATE('2000-01-01 11:23:52'); |
expression is an expression of the string type
that represents Datetime, Time or DateTimeOffset value. |
Bitwise Functions
Function | Definition | Example | Remarks |
---|---|---|---|
BITWISEAND(x_expr, y_expr) | Returns the bitwise conjunction of 'x_expr' and 'y_expr'. |
The following example return bitwise conjunction of two integer numbers: SELECT BITWISEAND(2,5); |
x_expr, y_expr are expressions of the integer types or expressions of a type
that can be implicitly converted to integer types. |
BITWISEOR(x_expr, y_expr) | Returns the bitwise disjunction of 'x_expr' and 'y_expr'. |
The following example return bitwise disjunction of two integer numbers: SELECT BITWISEOR(2,5); |
x_expr, y_expr - are expressions of the integer types or expressions of a type that can be implicitly converted to integer types. |
BITWISEXOR(x_expr, y_expr) | Returns the bitwise exclusive disjunction of 'x_expr' and 'y_expr'. |
The following example return bitwise exclusive disjunction of two integer numbers: SELECT BITWISEXOR(2,5); |
x_expr, y_expr are expressions of the integer types or expressions of a type that
can be implicitly converted to integer types. |
BITWISENOT(x_expr) | Returns the bitwise negation of 'x_expr'. |
The following example return bitwise bitwise negation integer number: SELECT BITWISENOT(5); |
x_expr is an expression of the integer types or expression of a type that can
be implicitly converted to integer types. |
Other Functions
Function | Definition | Example | Remarks |
---|---|---|---|
NEWGUID(x_expr) | Returns a new GUID as string. |
The following example return bitwise bitwise negation integer number: SELECT NEWGUID(); |