functions – Page 95

How SQLite Rtrim() Works

May 28, 2020May 17, 2020 by Ian

In SQLite, the rtrim() function enables you to trim whitespace or other characters from the right part of a string.

It returns a copy of the string that you provide as an argument, with the right part trimmed of either whitespace, or other characters as specified.

How SQLite Ltrim() Works

May 28, 2020May 17, 2020 by Ian

The SQLite ltrim() function trims whitespace or other characters from the left of a string.

More precisely, it returns a copy of the string that you provide as an argument, with the left part trimmed of either whitespace, or other characters that you specify.

SQLite Like() Function with Examples

May 28, 2020May 14, 2020 by Ian

The SQLite like() function enables you to compare a string against a given pattern. You can also specify an escape character if required.

You can use the like() function as an alternative to the LIKE operator (which enables you to run the Y LIKE X [ESCAPE Z] expression).

Tweak your Avg() Results in SQLite with the DISTINCT Keyword

May 28, 2020May 11, 2020 by Ian

If you know about the avg() function in SQLite, you’re probably aware that it returns the average of all non-NULL X within a group.

But did you know you can add the DISTINCT keyword to this function?

If you add the DISTINCT keyword, avg() will calculate its results based on distinct values only. This is essentially the same as removing duplicate values and then calculating the average on the remaining values.

How to Add Just the Distinct Values with SQLite Sum()

May 28, 2020May 10, 2020 by Ian

In SQLite, the Sum() function accepts an optional DISTINCT keyword that enables you to add just the distinct values in the group. That is, it removes any duplicates from its calculation.

So if there are say, three rows that contain 10, only one of those rows will be included in the results.

Remove Duplicates from Count() Results in SQLite

May 28, 2020May 10, 2020 by Ian

When using the count() function in SQLite, you might find yourself in the situation where you only want to count distinct values. That is, you don’t want duplicate values to be counted multiple times.

SQLite Sum() vs Total(): What’s the Difference?

May 28, 2020May 8, 2020 by Ian

SQLite has a function called sum() and another function called total(). Both functions do pretty much the same thing – they return the sum of all non-NULL values in a group.

But there is a subtle difference between these two functions.

The difference is in the way they handle NULL inputs.

How SQLite Total() Works

May 28, 2020May 8, 2020 by Ian

In SQLite, the total() function returns the sum of all non-NULL values in a group.

If there are no non-NULL values, then it returns 0.0.

This function is similar to the sum() function, except in the way it handles NULL inputs. When there are no non-NULL values, then sum() returns NULL (instead of 0.0 like the total() function returns).

How SQLite Sum() Works

May 28, 2020May 8, 2020 by Ian

The SQLite sum() function returns the sum of all non-NULL values in a group.

If there are no non-NULL values, then it returns NULL.

This function basically enables you to add up all the values in a result set or a table.

How SQLite Min() Works

May 28, 2020May 7, 2020 by Ian

The SQLite min() function returns the minimum non-NULL value from all values in a group.

The minimum value is the value that would appear first in a query that uses an ORDER BY clause on the same column.