357
HTML DEVELOPER’S GUIDE FOR ADOBE AIR
SQL support in local databases
Last updated 9/28/2011
Scalar functions
Scalar functions operate on values one row at a time.
Date and time formatting functions
The date and time formatting functions are a group of scalar functions that are used to create formatted date and time
data. Note that these functions operate on and return string and number values. These functions are not intended to
be used with the DATE data type. If you use these functions on data in a column whose declared data type is DATE,
they do not behave as expected.
ABS(X) Returns the absolute value of argument X.
COALESCE(X, Y, ...) Returns a copy of the first non-NULL argument. If all arguments are NULL then NULL is
returned. There must be at least two arguments.
GLOB(X, Y) This function is used to implement the X GLOB Y syntax.
IFNULL(X, Y) Returns a copy of the first non-NULL argument. If both arguments are NULL then NULL is
returned. This function behaves the same as COALESCE().
HEX(X) The argument is interpreted as a value of the BLOB storage type. The result is a hexadecimal
rendering of the content of that value.
LAST_INSERT_ROWID(
)
Returns the row identifier (generated primary key) of the last row inserted to the database
through the current SQLConnection. This value is the same as the value returned by the
SQLConnection.lastInsertRowID property.
LENGTH(X) Returns the string length of X in characters.
LIKE(X, Y [, Z]) This function is used to implement the X LIKE Y [ESCAPE Z] syntax of SQL. If the optional
ESCAPE clause is present, then the function is invoked with three arguments. Otherwise, it is
invoked with two arguments only.
LOWER(X) Returns a copy of string X with all characters converted to lower case.
LTRIM(X) LTRIM(X, Y) Returns a string formed by removing spaces from the left side of X. If a Y argument is specified,
the function removes any of the characters in Y from the left side of X.
MAX(X, Y, ...) Returns the argument with the maximum value. Arguments may be strings in addition to
numbers. The maximum value is determined by the defined sort order. Note that MAX() is a
simple function when it has 2 or more arguments but is an aggregate function when it has a
single argument.
MIN(X, Y, ...) Returns the argument with the minimum value. Arguments may be strings in addition to
numbers. The minimum value is determined by the defined sort order. Note that MIN() is a
simple function when it has 2 or more arguments but is an aggregate function when it has a
single argument.
NULLIF(X, Y) Returns the first argument if the arguments are different, otherwise returns NULL.
QUOTE(X) This routine returns a string which is the value of its argument suitable for inclusion into
another SQL statement. Strings are surrounded by single-quotes with escapes on interior
quotes as needed. BLOB storage classes are encoded as hexadecimal literals. The function is
useful when writing triggers to implement undo/redo functionality.
RANDOM(*) Returns a pseudo-random integer between -9223372036854775808 and
9223372036854775807. This random value is not crypto-strong.
RANDOMBLOB(N) Returns an N-byte BLOB containing pseudo-random bytes. N should be a positive integer. This
random value is not crypto-strong. If the value of N is negative a single byte is returned.
ROUND(X) ROUND(X,
Y)
Rounds off the number X to Y digits to the right of the decimal point. If the Y argument is
omitted, 0 is used.
RTRIM(X) RTRIM(X, Y) Returns a string formed by removing spaces from the right side of X. If a Y argument is
specified, the function removes any of the characters in Y from the right side of X.
SUBSTR(X, Y, Z) Returns a substring of input string X that begins with the Y-th character and which is Z
characters long. The left-most character of X is index position 1. If Y is negative the first
character of the substring is found by counting from the right rather than the left.
TRIM(X) TRIM(X, Y) Returns a string formed by removing spaces from the right side of X. If a Y argument is
specified, the function removes any of the characters in Y from the right side of X.
TYPEOF(X) Returns the type of the expression X. The possible return values are 'null', 'integer', 'real', 'text',
and 'blob'. For more information on data types see Data type support.
UPPER(X) Returns a copy of input string X converted to all upper-case letters.
ZEROBLOB(N) Returns a BLOB containing N bytes of 0x00.