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.

Name Description
SUM Calculates the sum of all values in a given column of a table.
AVG Calculates the average value of a given column of a table.
MAX Returns the maximum value in a given column of a table.
MIN Returns the minimum value in a given column of a table.
COUNT Returns the number of rows that match a specified condition or all rows in a table.
GROUP_CONCAT Concatenates the values of a given column of a table into a single string, separated by a specified delimiter.
TOTAL Calculates 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.

Name Description
DATE Extracts the date portion of a date/time expression, returning a string in the format “YYYY-MM-DD”.
TIME Extracts the time portion of a date/time expression, returning a string in the format “HH:MM:SS”.
DATETIME This function combines a date and time expression into a single string in the format “YYYY-MM-DD HH:MM:SS”.
STRFTIME This function formats a date/time expression according to a specified format string.
JULIANDAY This 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.

Name Description
ROW_NUMBER Assigns a unique sequential number to each row within a result set.
RANK Assigns 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_RANK This function is similar to RANK() but does not leave gaps in the ranking sequence when there are ties.
NTILE Divides 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.
LAG Returns the value of a specified column from the previous row in the result set.
LEAD Returns the value of a specified column from the next row in the result set.
FIRST_VALUE Returns the value of a specified column from the first row in the result set.
LAST_VALUE Returns 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.

Name Description
LENGTH Returns the length of a string in characters.
UPPER Converts a string to uppercase.
LOWER Converts a string to lowercase.
SUBSTR Extracts a substring from a string based on a specified starting position and length.
REPLACE Replaces all occurrences of a specified string with another string in a given string.
TRIM Removes 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.

Name Description
ABS Returns the absolute value of a number.
ROUND Rounds a number to a specified number of decimal places.
CEIL Returns the smallest integer greater than or equal to a given number.
FLOOR Returns the largest integer less than or equal to a given number.
EXP Returns the exponential value of a given number.
LOG Returns the natural logarithm of a given number.
POWER Returns the result of raising a given number to a specified power.
SQRT Returns the square root of a given number.
SIN Returns the sine value of a given angle.
COS Returns the cosine value of a given angle.
TAN Returns the tangent value of a given angle.
ASIN Returns the arc sine (inverse sine) of a given number.
ACOS Returns the arc cosine (inverse cosine) of a given number.
ATAN Returns 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.

Name Description
JSON Converts a JSON-formatted string into a SQLite value.
JSON_EXTRACT Extracts a value from a JSON object or array.
JSON_ARRAY Creates a new JSON array from a list of values.
JSON_OBJECT Creates a new JSON object from a list of key-value pairs.
JSON_TYPE Returns 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.