{"id":989,"date":"2016-05-19T11:22:43","date_gmt":"2016-05-19T04:22:43","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=989"},"modified":"2024-04-14T16:18:12","modified_gmt":"2024-04-14T09:18:12","slug":"sqlite-functions","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-functions\/","title":{"rendered":"SQLite Functions"},"content":{"rendered":"\n

This section provides the core SQLite functions including aggregate functions<\/a>, date & time functions<\/a>, string functions<\/a>, and math functions<\/a>.<\/p>\n\n\n

<\/div>
\n\t\t\t\t

\n\t\t\t\t\tSQLite Aggregate Functions<\/a>\n\t\t\t\t<\/h3>\n\t\t\t\t
This tutorial shows you how to use the SQLite aggregate functions to find the maximum, minimum, average, sum, and count of a set of values.<\/div>\n\t\t\t<\/div>\n\t\t<\/article>
<\/div>
\n\t\t\t\t

\n\t\t\t\t\tSQLite Date Functions<\/a>\n\t\t\t\t<\/h3>\n\t\t\t\t
This section provides you with SQLite date and time functions that help you manipulate datetime data effectively.<\/div>\n\t\t\t<\/div>\n\t\t<\/article>
<\/div>
\n\t\t\t\t

\n\t\t\t\t\tSQLite String Functions<\/a>\n\t\t\t\t<\/h3>\n\t\t\t\t
This section shows the most commonly used SQLite string functions that help you manipulate character string data effectively.<\/div>\n\t\t\t<\/div>\n\t\t<\/article>
<\/div>
\n\t\t\t\t

\n\t\t\t\t\tSQLite Window Functions<\/a>\n\t\t\t\t<\/h3>\n\t\t\t\t
SQLite window functions perform a calculation on a set of rows that are related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single result row.<\/div>\n\t\t\t<\/div>\n\t\t<\/article><\/section>\n\n\n\n

SQLite aggregate functions<\/a><\/h2>\n\n\n\n
Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
AVG<\/a><\/td>Return the average value of non-null values in a group<\/td><\/tr>
COUNT<\/a><\/td>Return the total number of rows in a table.<\/td><\/tr>
MAX<\/a><\/td>Return the maximum value of all values in a group.<\/td><\/tr>
MIN<\/a><\/td>Return the minimum value of all values in a group.<\/td><\/tr>
SUM<\/a><\/td>Return the sum of all non-null values in a column<\/td><\/tr>
GROUP_CONCAT<\/a><\/td>Concatenate non-null values in a column<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

SQLite string functions<\/a><\/h2>\n\n\n\n
Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
SUBSTR<\/a><\/td>Extract and return a substring with a predefined length starting at a specified position in a source string<\/td><\/tr>
TRIM<\/a><\/td>Return a copy of a string with specified characters removed from the beginning and the end of a string.<\/td><\/tr>
LTRIM<\/a><\/td>Return a copy of a string that has specified characters removed from the beginning of a string.<\/td><\/tr>
RTRIM<\/a><\/td>Return a copy of a string that has specified characters removed from the end of a string.<\/td><\/tr>
LENGTH<\/a><\/td>Return the number of characters in a string or the number of bytes in a BLOB.<\/td><\/tr>
REPLACE<\/a><\/td>Return a copy of a string with each instance of a substring replaced by the other substring.<\/td><\/tr>
UPPER<\/a><\/td>Return a copy of a string with all of the characters converted to uppercase.<\/td><\/tr>
LOWER<\/a><\/td>Return a copy of a string with all of the characters converted to lowercase.<\/td><\/tr>
INSTR<\/a><\/td>Find\u00a0a substring in a string and return an integer indicating\u00a0the position of the first occurrence of the substring.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

SQLite control flow functions<\/h2>\n\n\n\n
Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
COALESCE<\/a><\/td>Return the first non-null argument<\/td><\/tr>
IFNULL<\/a><\/td>Provide the NULL if\/else construct<\/td><\/tr>
IIF<\/a><\/td>Add if-else logic to queries.<\/td><\/tr>
NULLIF<\/a><\/td>Return NULL if the first argument is equal to the second argument.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

SQlite date and time functions<\/h2>\n\n\n\n
Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
DATE<\/a><\/td>Calculate the date based on multiple date modifiers.<\/td><\/tr>
TIME<\/a><\/td>Calculate the time based on multiple date modifiers.<\/td><\/tr>
DATETIME<\/a><\/td>Calculate the date & time based on one or more date modifiers.<\/td><\/tr>
JULIANDAY<\/a><\/td>Return the Julian day<\/a>, which is the number of days since noon in Greenwich on November 24, 4714 B.C.<\/td><\/tr>
STRFTIME<\/a><\/td>Format the date based on a specified format string.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

SQLite math functions<\/h2>\n\n\n\n
Name<\/strong><\/td>Description<\/strong><\/td><\/tr>
ABS<\/a><\/td>Return the absolute value of a number.<\/td><\/tr>
RANDOM<\/a><\/td>Return a random integer value.<\/td><\/tr>
ROUND<\/a><\/td>Round off a floating value to a specified precision.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

For a complete list of math functions in SQLite, check this page<\/a>.<\/p>\n

\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t