SQLite functions

SQLite is a popular database management system that provides various built-in functions to manipulate and retrieve data from the database. These functions can be classified into different categories, including aggregate functions, date and time functions, window functions, string functions, math functions, and JSON functions.

Aggregate functions

Aggregate functions are used to perform calculations on a set of values and return a single result. SQLite provides a variety of aggregate functions such as COUNT, SUM, AVG, MAX, and MIN. These functions are commonly used in queries to retrieve statistics about the data in a table. For example, you can use the COUNT function to count the number of rows in a table, or the SUM function to calculate the total value of a column.

NameDescription
SUMCalculates the sum of all values in a given column of a table.
AVGCalculates the average value of a given column of a table.
MAXReturns the maximum value in a given column of a table.
MINReturns the minimum value in a given column of a table.
COUNTReturns the number of rows that match a specified condition or all rows in a table.
GROUP_CONCATConcatenates the values of a given column of a table into a single string, separated by a specified delimiter.
TOTALCalculates the sum of all values in a given column of a table, including NULL values.

Date and Time functions

SQLite provides various Date and Time functions to manipulate dates and times. Some of the common date and time functions include DATE, TIME, DATETIME, STRFTIME, and JULIANDAY. These functions allow you to perform operations such as converting dates and times to different formats, extracting components such as the day or month from a date, and calculating the difference between two dates.

NameDescription
DATEExtracts the date portion of a date/time expression, returning a string in the format “YYYY-MM-DD”.
TIMEExtracts the time portion of a date/time expression, returning a string in the format “HH:MM:SS”.
DATETIMEThis function combines a date and time expression into a single string in the format “YYYY-MM-DD HH:MM:SS”.
STRFTIMEThis function formats a date/time expression according to a specified format string.
JULIANDAYThis function returns the Julian day number for a given date/time expression.

Window functions

Window functions are used to perform calculations on a specific set of rows in a result set. SQLite supports several window functions, including ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions are commonly used in analytical queries to perform calculations such as running totals, moving averages, and rank order.

NameDescription
ROW_NUMBERAssigns a unique sequential number to each row within a result set.
RANKAssigns a rank to each row within a result set based on the value of a specified column. Rows with the same value in the specified column receive the same rank.
DENSE_RANKThis function is similar to RANK() but does not leave gaps in the ranking sequence when there are ties.
NTILEDivides the result set into a specified number of groups, assigning each row to one of the groups based on the value of a specified column.
LAGReturns the value of a specified column from the previous row in the result set.
LEADReturns the value of a specified column from the next row in the result set.
FIRST_VALUEReturns the value of a specified column from the first row in the result set.
LAST_VALUEReturns the value of a specified column from the last row in the result set.

String functions

String functions are used to manipulate strings in the database. SQLite provides several built-in string functions such as SUBSTR, LENGTH, UPPER, LOWER, and REPLACE. These functions allow you to perform operations such as extracting a substring from a string, converting a string to upper or lower case, and replacing a substring with another string.

NameDescription
LENGTHReturns the length of a string in characters.
UPPERConverts a string to uppercase.
LOWERConverts a string to lowercase.
SUBSTRExtracts a substring from a string based on a specified starting position and length.
REPLACEReplaces all occurrences of a specified string with another string in a given string.
TRIMRemoves leading and trailing whitespace characters from a string.

Math functions

Math functions are used to perform mathematical calculations on numeric data. SQLite provides various built-in math functions, such as ABS, ROUND, CEIL, FLOOR, and RANDOM. These functions allow you to perform operations such as finding the absolute value of a number, rounding a number to a specific decimal place, and generating a random number.

NameDescription
ABSReturns the absolute value of a number.
ROUNDRounds a number to a specified number of decimal places.
CEILReturns the smallest integer greater than or equal to a given number.
FLOORReturns the largest integer less than or equal to a given number.
EXPReturns the exponential value of a given number.
LOGReturns the natural logarithm of a given number.
POWERReturns the result of raising a given number to a specified power.
SQRTReturns the square root of a given number.
SINReturns the sine value of a given angle.
COSReturns the cosine value of a given angle.
TANReturns the tangent value of a given angle.
ASINReturns the arc sine (inverse sine) of a given number.
ACOSReturns the arc cosine (inverse cosine) of a given number.
ATANReturns the arc tangent (inverse tangent) of a given number.

JSON functions

SQLite provides several built-in JSON functions to work with JSON data. Some of the common JSON functions include JSON(), JSON_ARRAY(), JSON_OBJECT(), and JSON_EXTRACT(). These functions allow you to create and manipulate JSON objects and arrays, and extract data from JSON strings.

NameDescription
JSONConverts a JSON-formatted string into a SQLite value.
JSON_EXTRACTExtracts a value from a JSON object or array.
JSON_ARRAYCreates a new JSON array from a list of values.
JSON_OBJECTCreates a new JSON object from a list of key-value pairs.
JSON_TYPEReturns the type of a JSON value.

In conclusion, SQLite provides a comprehensive set of built-in functions that enable you to perform various data manipulation and retrieval tasks. Whether you need to perform calculations on numeric data, manipulate strings, or work with date and time data, SQLite’s built-in functions can help you accomplish your goals efficiently and effectively.