Functions that can be used in a query

You can use the functions provided by SQLite3.

For the functions that can be used in a query, see Table 3.2 List of computational functions.

Table 3.2 List of computational functions

Aggregate Functions:

Name of function	Use	Remarks
avg(X)	Returns the average of the specified column	Handles a character that is not a numeric character as 0. Data with no value are excluded from the comparison targets and the function returns the result being divided by the number of rows excluding the data without values.
count(*)	Returns the number of rows in the table.
count(X)	Returns the number of rows that are not NULL in the specified column.
max(X)	Returns the maximum value of the specified column.	Handles a character that is not a numeric character as 0.
min(X)	Returns the minimum value of the specified column.	Handles a character that is not a numeric character as 0.
sum(X)	Returns the total sum of the specified column.	Handles a character that is not a numeric character as 0.
total(X)	Returns the total sum of the specified column as a floating-point number
group_concat(X) group_concat(X,Y)	Returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then Y is used as the separator between instances of X. A comma (,) is used as the separator if Y is omitted.

Date And Time Functions:

Name of function	Use	Remarks
date(X,Y,Z, …)	Returns the date data specified for X in the YYYY-MM-DD format. You can use Y and Z to specify how to process the date and time. Addition and subtraction of the date and time: NNN years NNN months NNN days NNN hours NNN minutes NNN.NNNN seconds #NNN: Use +n or -n to specify the date and time to add or subtract. First day and last day: start of year start of month start of day Date of the specified day of the week: weekday N #N:Specify a numeric value that represents a day of the week.0 represents Sunday and 6 is Saturday. Date of the UNIX time stamp: unixepoch Display in the local time zone: localtime Display in the UTC time zone: utc	When you use a character string type, only the following formats are valid. YYYY-MM-DD YYYY-MM-DD HH:MI YYYY-MM-DD HH:MI:SS YYYY-MM-DD HH:MI:SS.SSS If the data is of the date type, the data is converted to the YYYY-MM-DD HH:MI:SS format internally. For details about the applicable date types, see Date type.
time(X,Y,Z, ...)	Returns the time data specified for X in the HH:MM:SS format. The usage of Y and Z is same as the date function.
datetime(X,Y,Z,...)	Returns the date and time data specified for X in the YYYY-MM-DD HH:MM:SS format. The usage of Y and Z is same as the date function.
strftime(X,Y,Z,...)	Returns the date and time data specified for Y in the format specified for X. The usage of Y and Z is same as the date function.

Core Function

Name of function	Use	Remarks
abs(x)	Returns the absolute value of the numeric value.
coalesce(X,Y,...)	Among the 2 or more columns that have been specified, returns the first column that is not NULL.
glob(X,Y)	If Y matches the pattern specified by X, returns 1. If not, returns 0.
ifnull(X,Y)	Among the 2 columns that have been specified, returns the first column that is not NULL.
hex(X)	Returns the value of the BLOB type in a hex.
like(X,Y)	If Y matches the pattern specified by X, returns 1. If not, returns 0.	This function is not case sensitive.
like(X,Y,Z)	If Y matches the pattern specified by X except Z, returns 1. If not, returns 0.
lower(X)	Converts the character string specified for the argument to lower case and returns the result.
upper(X)	Converts the character string specified for the argument to upper case and returns the result.
ltrim(X)	Returns a character string with half-width spaces stripped off the beginning of X.
ltrim(X,Y)	Returns a character string with any and all characters inside Y stripped off only the beginning of X.
round(X,Y)	Rounds off the numeric value (X) to the specified number of decimal places (Y) and returns the result. For example, when 2 is specified for Y, the result will be a decimal number rounded to two decimal places.
rtrim(X)	Returns a character string with half-width spaces stripped off the end of X.
rtrim(X,Y)	Returns a character string with any and all characters inside Y stripped off only the end of X.
substr(X,Y,Z)	Obtains a substring with the specified number of characters (Z) starting from the specified position (Y) of the character string (X).
trim(X)	Returns a character string with half-width spaces stripped off both the beginning and end of X.
trim(X,Y)	Returns a character string with any and all characters inside Y stripped off the beginning and end of X.
max(X,Y,...)	Returns the maximum value of the specified argument.
min(X,Y,...)	Returns the minimum value of the specified argument.
nullif(X,Y)	Compares X and Y and if the values are same, returns NULL, and if the values are different, returns X.
quote(X)	Returns the value enclosed in singe quotation marks.	If a numeric type is specified, returns the value as it is. If the value contains ' (1 single quotation mark), converts the single quotation mark to '' (2 single quotation marks) and returns the result.
random()	Generates a random number and returns the value.
replace(X,Y,Z)	Replaces the specified character (Y) in the character string (X) with another specified character (Z).

Note

The operations of computational functions that are not listed in the table are not guaranteed.

Table 3.3 Operator
Operator	How to use	Description
+	A+B	Adds B to A.
-	A-B	Subtracts B from A.
*	A*B	Multiplies A and B.
/	A/B	Divides A by B.
%	A%B	Remainder when A is divided by B.

= Remarks =

To use reference types for the input file, matching file and variables (such as $INUM and $INAME) within a function and combine the value with a specified character string for evaluation, use || (pipe) to connect the character strings.

Example) Conducting a fuzzy search by using % in the like(X,Y) function

　select [col1] from [vt] where like('%'||'ABC'||'%',[col1])