SQLite functions – SQLite Tutorial

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.