Mastering in SQLite

Functions

Before getting into SQLite Functions, if you haven’t covered the previous topic-Subquery, then get into it.

To visit the introduction page to see the available topics click here

A number of superheroes are available in our DB, click here to download it.

SQLite Functions

SQLite has many built-in functions. These built-in functions are case insensitive which means it can be used in lower-case form or upper-case form or mixed form.

The various built-in functions can be classified into the following types,

1. SQLite Math Functions
2. SQLite String Functions
3. SQLite Date and Time Functions
4. SQLite Advanced Functions

1. SQLite Math Functions

SUM()

The SUM() function returns the sum of all non-NULL values. There is a similar function like SUM(), and that is TOTAL().

The difference between them is, The result of TOTAL() is always a floating-point value whereas the result of SUM() is an integer if all non-NULL inputs are integers else it returns a floating-point value.

Another difference is SUM() will throw an “integer overflow” exception when an integer overflow occurs whereas the TOTAL() doesn’t.

SELECT
    SUM(salary)
FROM
    charactersDetails;

OUTPUT

SELECT
    TOTAL(salary)
FROM
    charactersDetails;

OUTPUT

AVG()

The AVG() function returns the average value of all non-NULL in a column. The result of AVG() is always a floating-point when there is at least one non-NULL value else the result is NULL. String and BLOB[binary large object] values that do not look like a number are interpreted as 0.

SELECT
    AVG(salary)
FROM
    charactersDetails;

OUTPUT

ABS()

The ABS() function returns the absolute value of a non-NULL value. If the value is NULL then ABS() returns NULL. String and BLOB values that don’t look like numbers are interpreted as 0.

SELECT
    ABS(-1357);

OUTPUT

COUNT()

The COUNT() function returns the number of non-NULL values of a column.

Whereas the COUNT(*) function returns the number of rows in the table including non-NULL values.

SELECT
    COUNT(superPower)
FROM
    charactersDetails;

OUTPUT

SELECT
    COUNT(*)
FROM
    charactersDetails;

OUTPUT

MIN()

The MIN() function returns the minimum non-NULL value of a column. It returns NULL only if all the values are NULL in that column.

SELECT
    name,
    MIN(salary)
FROM
    charactersDetails;

OUTPUT

MAX()

The MAX() function returns the maximum value of a column. It returns NULL only if all the values are NULL in that column.

SELECT
    name,
    MAX(salary)
FROM
    charactersDetails;

OUTPUT

RANDOM()

The RANDOM() function returns a random integer between -9223372036854775808 and +9223372036854775807, as these are the minimum and maximum integer values.

SELECT
    id,
    name,
    yearCreated
FROM
    characterList
ORDER BY
    RANDOM()
LIMIT
    5;

OUTPUT

ROUND()

The ROUND() function returns a floating-point value rounded to the mentioned digits. If the precision is not mentioned then 0 is taken by default.

SELECT
    ROUND(1234.567, 2);

OUTPUT

2. SQLite String Functions

INSTR()

The INSTR() function finds the first occurrence of a string within another string and returns its position. If the string is not present or any of the string is NULL then INSTR() returns NULL.

SELECT
    id,
    movieName,
    boxOfficeStatus,   
    INSTR(movieName, 'Man')
FROM
    movieDetails
WHERE
    INSTR(moviename, 'Man');

OUTPUT

LENGTH()

The LENGTH() function returns the number of characters in a string. If the string is NULL then LENGTH() returns NULL.

SELECT
    movieName,
    castAndCrew,   
    Length(castAndCrew)
FROM
    movieDetails
ORDER BY
    LENGTH(castAndCrew) DESC;

OUTPUT

LOWER()

The LOWER() function returns a copy of a string with all ASCII characters converted to lowercase. For non-ASCII characters ICU extension of this function is used.

SELECT
    id,
    movieName,
    directorName,
    LOWER(directorName)
FROM
    movieDetails;

OUTPUT

UPPER()

The UPPER() function returns a copy of a string with all ASCII characters converted to uppercase. For non-ASCII characters ICU extension of this function is used.

SELECT
    id,
    movieName,
    directorName,
    UPPER(directorName)
FROM
    movieDetails;

OUTPUT

TRIM()

The TRIM() function returns a copy of a string that has specified characters removed from the beginning and the end of a string. If no characters are specified then it removes spaces from the beginning and the end of a string.

SELECT
    TRIM('#DC vs #Marvel##', '#')

OUTPUT

LTRIM()

The LTRIM() function returns a copy of a string that has specified characters removed from the beginning of a string. If no characters are specified then it removes spaces from the beginning of a string.

SELECT
    LTRIM('#DC vs #Marvel##', '#')

OUTPUT

RTRIM()

The RTRIM() function returns a copy of a string that has specified characters removed from the end of a string. If no characters are specified then it removes spaces from the end of a string.

SELECT
    RTRIM('#DC vs #Marvel##', '#')

OUTPUT

SUBSTR()

The SUBSTR() function returns a substring with a specified length at a specified location. If the length is not specified then it returns all the characters till the end.

SELECT
    movieName,
    castAndCrew,
    SUBSTR(castAndCrew, 1, 12)
FROM
    movieDetails
WHERE
    movieName = 'xMen';

OUTPUT

REPLACE()

The REPLACE() function returns a copy of a string with each instance of a substring replaced by the other substring. If the substring to be replaced is empty then it returns the same string without any changes.

UPDATE
    charactersDetails
SET
    realname = REPLACE(realname, 'Clark Kent', 'Henry Cavill');

OUTPUT

3. SQLite Date and Time Functions

DATE()

The DATE() function takes a time string and zero or more modifiers. It returns a date string in YYYY-MM-DD format.

SELECT 
    DATE('now');

OUTPUT

There are various time strings available. Here, “now”is a time string that specifies the current date.

Some other examples of time strings are

Computing the date of the first Wednesday in August for the current year

SELECT 
    DATE('now', 'start of year', '+7 months', 'weekday 3');

OUTPUT

TIME()

The TIME() function takes a time string and zero or more modifiers. It returns a time string in HH:MM:SS format.

SELECT 
    TIME('now', 'localtime');

OUTPUT

Computing the time when 2 hours and 30 minutes is added to the current local time.

SELECT 
    TIME('now', 'localtime', '+2 hours', '+30 minutes');

OUTPUT

DATETIME()

The DATETIME() function takes a time string and zero or more modifiers. It returns a date string in YYYY-MM-DD HH:MM:SS format.

SELECT 
    DATETIME('now', 'localtime');

OUTPUT

Calculating the current time two days before. Which is same as of now

SELECT 
    DATETIME('now', '-2 day', 'localtime');

OUTPUT

JULIAN()

The JULIAN() function returns the Julian day, which is the number of days since noon in Greenwich on November 24, 4714 B.C.

SELECT 
    JULIANDAY('now');

OUTPUT

STRFTIME()

The STRFTIME() function formats a DATETIME value based on a specified format.

SELECT 
    STRFTIME('%d','now');

OUTPUT

SELECT 
    STRFTIME('%m','now');

OUTPUT

4. SQLite Advanced Functions

COALESCE()

The COALESCE() function returns the first non-NULL value. If all values are NULL then it returns NULL. This function accepts two or more values.

SELECT
    id,
    name,
    COALESCE(realname, 'unknown')
FROM
    charactersDetails;

OUTPUT

IFNULL()

The IFNULL() is equivalent to COALESCE() with just two values. It returns NULL if both the values are NULL.

SELECT
    id,
    name,
    IFNULL(realname, 'unknown')
FROM
    charactersDetails;

OUTPUT

NULLIF()

The NULLIF() function takes two values and returns the first value if both are different. It returns NULL if both the values are the same.

SELECT 
    COUNT(NULLIF(salary, 0)) knownSalary
FROM
    charactersDetails;

OUTPUT

Reach out the next topic-Views

To learn more about functions in detail visit the official SQLite documentation.

Dhayal Raju