dotConnect for SQLite Documentation
On this page
    Standard SQL Function Support
    On this page

    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

    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.

    VAR can be used with numeric columns only.

    Null values are ignored.

    This function returns NULL if there were no matching rows.

    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.

    VARP can be used with numeric columns only.

    Null values are ignored.

    This function returns NULL if there were no matching rows.

    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.

    STDEV can be used with numeric columns only.

    Null values are ignored.

    This function returns NULL if there were no matching rows.
    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.

    STDEVP can be used with numeric columns only.

    Null values are ignored.

    This function returns NULL if there were no matching rows.

    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), 
    SELECT FLOOR(-12.9);
                
    SELECT FLOOR('12.9')

    For integer arguments, the return value has integer type.

    For string or floating-point arguments, the return value has a floating-point type.

    Returns NULL if expession is NULL;

    Return Types: The floating-point 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), 
    SELECT CEILING(-12.9);
                
    SELECT CEILING('12.9')

    For integer arguments, the return value has integer type.

    For string or floating-point arguments, the return value has a floating-point type.

    Returns NULL if expession is NULL;

    Return Types: The floating-point 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);
              
    SELECT POWER(2,  2.5);

    expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.

    y is a power to which expression will be raised.

    Returns NULL if expession is NULL or y is NULL.

    Return Types: The type of expression.

    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.

    digits is a value of integer type.

    Returns NULL if expession is NULL or digits is NULL.

    Return Types: The type of expression.

    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.
    Returns NULL if expession is NULL or expession < 0;

    Return Types: The type of expression.
    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Values outside this range return NULL.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Values outside this range return NULL

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession1 is NULL or expession2 is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    The constant e (2.718281?), is the base of natural logarithms.

    The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

    The exponential of the natural logarithm of a number is the number itself: EXP (LOG (n)) = n. And the natural logarithm of the exponential of a number is the number itself: LOG (EXP (n)) = n.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    The constant e (2.71828182845905?) is the base of natural logarithms.

    The base of natural logarithms is the constant e (2.71828182845905?). LOG ( e ) = 1.0.

    The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( n ) ) = n.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    base is an expression of the type float or of a type that can be implicitly converted to floating-point type.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(n) = n.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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.

    Returns NULL if expession is NULL;

    Return Types: The integer 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.

    Returns NULL if expession is NULL;

    Return Types: The type of expression.

    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'.

    Returns NULL when 'substr' or 'str' are NULL.

    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'.

    Returns NULL when 'substr' or 'str' are NULL.

    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'.

    Returns NULL when 'substr' or 'str' are NULL.

    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'.

    Returns NULL when 'substr' or 'str' are NULL.

    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'.

    Returns NULL when 'substr' or 'str' are NULL.

    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
     ADDNANOSECONDS('11:23:52.1234568', 500);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    100 nanoseconds is equal to 0.0000001 (10^-7) second.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDMICROSECONDS(expression, number) Adds the specified 'number' of microseconds to the 'expression'.

    The following example adding 500 microseconds to the specified time:

    SELECT
     ADDMICROSECONDS('11:23:52.1234568', 500);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDMILLISECONDS (expression, number) Adds the specified 'number' of milliseconds to the 'expression'.

    The following example adding 500 milliseconds to the specified time:

    SELECT
     ADDMILLISECONDS('11:23:52.1234568', 500);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDSECONDS (expression, number) Adds the specified 'number' of seconds to the 'expression'.

    TThe following example adding -12 seconds to the specified time:

    SELECT
     ADDSECONDS('11:23:52.1234568', -12);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDMINUTES (expression, number) Adds the specified 'number' of minutes to the 'expression'.

    The following example adding 10 minutes to the specified time:

    SELECT
     ADDMINUTES('11:23:52.1234568', 10);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDMINUTES (expression, number) Adds the specified 'number' of minutes to the 'expression'.

    The following example adding 10 minutes to the specified time:

    SELECT
     ADDMINUTES ('11:23:52.1234568', 10);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDHOURS (expression, number) Adds the specified 'number' of hours to the 'expression'.

    The following example adding -5 hours to the specified time:

    SELECT ADDHOURS
     ('11:23:52.1234568', -5);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDDAYS (expression, number) Adds the specified 'number' of days to the 'expression'.

    The following example adding 5 days to the specified time:

    SELECT ADDDAYS
      ('2000-01-01 11:23:52.1234568', 5);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDMONTHS (expression, number) Adds the specified 'number' of months to the 'expression'.

    The following example adding 5 months to the specified time:

    SELECT
     ADDMONTHS('2000-01-01 11:23:52.1234568', 5);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    ADDYEARS (expression, number) Adds the specified 'number' of years to the 'expression'.

    The following example adding 5 years to the specified time:

    SELECT
     ADDYEARS ('2000-01-01 11:23:52.1234568', 5);

    expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

    Returns NULL when 'expression' or 'number' is NULL.

    Return Value: a string representation of DateTime, Time, DateTimeOffset.

    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.

    Return Value: a string representation of DateTime.

    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)

    Returns NULL when any of arguments is NULL.

    Return Value: a string representation of DateTimeOffset.

    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.

    Returns NULL when any of arguments is NULL.

    Return Value: a string representation of Time.

    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.

    Returns NULL when 'startExpression' or 'endExpression' is NULL.

    Return Value: a string representation of time interval.

    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.

    Returns NULL when 'startExpression' or 'endExpression' is NULL.

    Return Value: a integer.

    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.

    Returns NULL when 'startExpression' or 'endExpression' is NULL.

    Return Value: a integer.

    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.

    Returns NULL when 'datetimeoffset' is NULL.

    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.

    Returns NULL when 'expression' is NULL.

    Return Type: a string representation date.

    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.

    Returns NULL when any expression is NULL.

    Return Types: The type of expressions.

    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.

    Returns NULL when any expression is NULL.

    Return Types: The type of expressions.

    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.

    Returns NULL when any expression is NULL.

    Return Types: The type of expressions.

    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.

    Returns NULL when expression is NULL.

    Return Types: The type of expression.

    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();

    See Also

    SQLite-specific articles  | 

     

     

    © 2008 - 2026 Devart. All Rights Reserved.

    [email protected]