Hi,
I've had these functions hanging around for some time. They are not fully
tested and come with no warranty of fitness, but if anyone wants the code,
please take it.
I have all the code as a MS Visual Studio 2003 project. It is based on
source code 3.3.5
abs(X) Return the absolute value of argument X.
acos(X) Return the angle Y in radians such that cos(Y)=X. The domain is
|X|≤1
acosh(X) Return the angle Y in radians such that cosh(Y)=X. The domain is
|X|≥1
asin(X) Return the angle Y in radians such that sin(Y)=X. The domain is
|X|≤1
asinh(X) Return the angle Y in radians such that sinh(Y)=X.
atan(X) Return the angle Y in radians such that tan(Y)=X.
atanh(X) Return the angle Y in radians such that tanh(Y)=X.
atn2(Y,X) Return the angle Z in radians such that tan(Z)=Y/X.
atan2(Y,X) An alias for atn2.
ceil(X) smallest integral value not less than X. eg: ceil(1.1)=2,
ceil(1.0)=1
charindex(X,Y,Z) given 2 strings X, Y and an integer Z (defaults to 1),
return the 1 based index such that from that index onward Y is equal to X
(for the length of X) and the index is greater than or equal to Z. If isn't
found, returns 0.
When X or Y are NULL returns NULL. When Z ≤ 0 assumes the search starts from
index 1.
eg: charindex("T","Teste",1)=1, charindex("st","Teste",1)=3,
charindex("T","Teste",2)=0
coalesce(X,Y,...) Return a copy of the first non-NULL argument. If all
arguments are NULL then NULL is returned. There must be at least 2
arguments.
cos(X) Return the cosine of the angle X where the angle is expressed in
radians.
cosh(X) Return the hyperbolic cosine of the angle X where the angle is
expressed in radians.
cot(X) Return the cotangent of the angle X where the angle is expressed in
radians.
coth(X) Return the hyperbolic cotangent of the angle X where the angle is
expressed in radians.
degrees(X) Returns the angle in degrees that corresponds to the angle X in
radians.
difference(X,Y) Returns the number of equal characters of the values of
soundex of X and Y.
exp(X) Returns E raised to the power X.
floor(X) largest integral value not greater than X.
eg: floor(1.9)=1, floor(1.0)=1
glob(X,Y) This function is used to implement the "X GLOB Y" syntax of
SQLite. The sqlite3_create_function() interface can be used to override this
function and thereby change the operation of the GLOB operator.
ifnull(X,Y) Return a copy of the first non-NULL argument. If both arguments
are NULL then NULL is returned. This behaves the same as coalesce() above.
last_insert_rowid() Return the ROWID of the last row insert from this
connection to the database. This is the same value that would be returned
from the sqlite_last_insert_rowid() API function.
leftstr(X,Y) Returns the Y first characters of the string X. When the length
of X is not greater than Y just returns X.
If X is NULL returns NULL.
eg: leftstr('123456789',2)='12', leftstr('123456789',20)='123456789'
length(X) Return the string length of X in characters. If SQLite is
configured to support UTF-8, then the number of UTF-8 characters is
returned, not the number of bytes.
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
user-function is invoked with three arguments. Otherwise, it is invoked with
two arguments only. The sqlite_create_function() interface can be used to
override this function and thereby change the operation of the LIKE
operator. When doing this, it may be important to override both the two and
three argument versions of the like() function. Otherwise, different code
may be called to implement the LIKE operator depending on whether or not an
ESCAPE clause was specified.
log(X) Returns Y such that exp(Y)=X.
The domain is X > 0
log10(X) Returns Y such that power(10,Y)=X.
The domain is X > 0
lower(X) Return a copy of string X will all characters converted to lower
case. The C library tolower() routine is used for the conversion, which
means that this function might not work correctly on UTF-8 characters.
ltrim(X) Returns a string equal to X but with all the whitespaces at the
begining removed.
Returns NULL when X is NULL.
eg: ltrim(' 1234 ')='1234 ', ltrim('1234 ')='1234 '
max(X,Y,...) Return the argument with the maximum value. Arguments may be
strings in addition to numbers. The maximum value is determined by the usual
sort order. Note that max() is a simple function when it has 2 or more
arguments but converts to an aggregate function if given only a single
argument.
min(X,Y,...) Return the argument with the minimum value. Arguments may be
strings in addition to numbers. The minimum value is determined by the usual
sort order. Note that min() is a simple function when it has 2 or more
arguments but converts to an aggregate function if given only a single
argument.
nullif(X,Y) Return the first argument if the arguments are different,
otherwise return NULL.
padc(X,Y) Returns the string X with added spaces at the begining and end so
that it's total length is Y. When the length of X is greater or equal ro Y
just returns X. The number spaces added at the begining and at the end will
differ at most by one unit.
Returns NULL when X is NULL.
eg: padc('a',3)=' a '
padl(X,Y) Returns the string X with added spaces at the begining so that
it's total length is Y. When the length of X is greater or equal ro Y just
returns X.
Returns NULL when X is NULL.
eg: padl('a',3)=' a'
padr(X,Y) Returns the string X with added spaces at the end so that it's
total length is Y. When the length of X is greater or equal ro Y just
returns X. Returns NULL when X is NULL.
eg: padr('a',3)='a '
pi() Returns the constant pi=acos(-1)=3.14159...
power(X,Y) Returns X raised to the power Y.
X must be positive.
proper(X) Returns the X with proper capitalization. This string will have an
upper character after every blank character and all other characters will be
in lowercase.
When X is NULL returns NULL.
eg: proper('SQLIte is a DATABASE')='Sqlite Is A Database'
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. BLOBs are encoded
as hexadecimal literals. The current implementation of VACUUM uses this
function. The function is also useful when writing triggers to implement
undo/redo functionality.
radians(X) Returns the angle in radians that corresponds to the angle X in
degrees.
random(*) Return a random integer between -2147483648 and +2147483647.
replace(X,Y,Z) Returns the string X with every occurence of Y replaced by Z.
When X is NULL returns NULL, when Y is NULL just returns X and when Z just
removes the occurences of Y.
eg: replace('sqlite','sql',NULL)='ite',
replace('sqlite','sql','SQL')='SQLite'
replicate(X, Y) Given a string X and an integer Y returns the string X
contatenated Y times.
When X is NULL returns NULL.
eg: replicate('abc',2)='abcabc'
reverse(X) Returns the X with all characters in reverse order.
When X is NULL returns NULL.
eg: reverse('abc')='cba'
rightstr(X,Y) Returns the Y last characters of the string X. When the length
of X is not greater than Y just returns X. When X is NULL returns NULL.
eg: rightstr('123456789',2)='89', rightstr('123456789',20)='123456789'
round(X)
round(X,Y) Round off the number X to Y digits to the right of the decimal
point. If the Y argument is omitted, 0 is assumed.
rtrim(X) Returns a string equal to X but with all the whitespaces at the end
removed.
Returns NULL when X is NULL.
eg: rtrim(' 1234 ')=' 1234', rtrim('1234 ')=' 1234'
sign(X) Returns the integer 1 if X>0, -1 if X<0 and 0 if X=0 .
Returns NULL when X is NULL.
sin(X) Return the sine of the angle X where the angle is expressed in
radians.
sinh(X) Return the hyperbolic sine of the angle X where the angle is
expressed in radians.
soundex(X) Compute the soundex encoding of the string X. The string "?000"
is returned if the argument is NULL. This function is omitted from SQLite by
default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used
when SQLite is built.
sqlite_version(*) Return the version string for the SQLite library that is
running. Example: "2.8.0"
sqrt(X) Returns the positive squareroot of X.
X can't be negative.
square(X) Returns the square of X.
strfilter(X,Y) Returns the string X with the characters not in Y removed.
Returns NULL whenever X or Y are NULL.
eg: strfilter('sqlite','lseq')='sqle'
substr(X,Y,Z) Return 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
number 1. If Y is negative the the first character of the substring is found
by counting from the right rather than the left. If SQLite is configured to
support UTF-8, then characters indices refer to actual UTF-8 characters, not
bytes.
tan(X) Return the tangent of the angle X where the angle is expressed in
radians.
tanh(X) Return the hyperbolic tangent of the angle X where the angle is
expressed in radians.
typeof(X) Return the type of the expression X. The only return values are
"null", "integer", "real", "text", and "blob". SQLite's type handling is
explained in Datatypes in SQLite Version 3.
trim(X) Returns a string equal to X but with all the whitespaces at the
begining and at the end removed.
Returns NULL when X is NULL.
eg: trim(' 1234 ')='1234'
upper(X) Return a copy of input string X converted to all upper-case
letters. The implementation of this function uses the C library routine
toupper() which means it may not work correctly on UTF-8 strings.
The aggregate functions shown below are available by default. Additional
aggregate functions written in C may be added using the
sqlite3_create_function() API.
In any aggregate function that takes a single argument, that argument can be
preceeded by the keyword DISTINCT. In such cases, duplicate elements are
filtered before being passed into the aggregate function. For example, the
function "count(distinct X)" will return the number of distinct values of
column X instead of the total number of non-null values in column X.
avg(X) Return the average value of all non-NULL X within a group. String and
BLOB values that do not look like numbers are interpreted as 0. The result
of avg() is always a floating point value even if all inputs are integers.
count(X)
count(*) The first form return a count of the number of times that X is not
NULL in a group. The second form (with no argument) returns the total number
of rows in the group.
lower_quartile(X) Returns the value of the group such that the number of
elements smaller is equal to one fourth of the total number of elements.
NULL values are ignored in the count.
When more than one value satisfies the previous condition, returns the
average of those values.
eg: if the sample is 1,2,3,4,5 the lower quartile is 2.
max(X) Return the maximum value of all values in the group. The usual sort
order is used to determine the maximum.
median(X) Returns the value of the group such that the number of elements
smaller is equal to the number of larger elements.
When more than one value satisfies the previous condition, returns the
average of those values.
NULL values are ignored in the count.
eg: if the sample is 1,2,3,4,5 the median is 3.
min(X) Return the minimum non-NULL value of all values in the group. The
usual sort order is used to determine the minimum. NULL is only returned if
all values in the group are NULL.
mode(X) Returns the most frequent value in the sample X.
When there is more than one most frequent element, returns NULL. NULL values
are ignored in the count.
eg: if the sample is 1,2,3 the mode is NULL, but with a sample 1,2,2,2,3,
the mode is 2.
stdev(X) Returns the standard deviation of the sample X.
sum(X)
total(X) Return the numeric sum of all non-NULL values in the group. If
there are no non-NULL input rows then sum() returns NULL but total() returns
0.0. NULL is not normally a helpful result for the sum of no rows but the
SQL standard requires it and most other SQL database engines implement sum()
that way so SQLite does it in the same way in order to be compatible. The
non-standard total() function is provided as a convenient way to work around
this design problem in the SQL language.
The result of total() is always a floating point value. The result of sum()
is an integer value if all non-NULL inputs are integers. If any input to
sum() is neither an integer or a NULL then sum() returns a floating point
value which might be an approximation to the true sum.
Sum() will throw an "integer overflow" exception if all inputs are integers
or NULL and an integer overflow occurs at any point during the computation.
Total() never throws an exception.
upper_quartile(X) Returns the value of the group such that the number of
elements larger is equal to one fourth of the total number of elements.
NULL values are ignored in the count.
When more than one value satisfies the previous condition, returns the
average of those values.
eg: if the sample is 1,2,3,4,5 the upper quartile is 4.
variance(X) Returns the variance of the sample X.
Sample C code:
void sqlite3RegisterExtraFunctions(sqlite3 *db){
static const struct {
char *zName;
signed char nArg;
u8 argType; /* 0: none. 1: db 2: (-1) */
u8 eTextRep; /* 1: UTF-16. 0: UTF-8 */
u8 needCollSeq;
void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
} aFuncs[] = {
/* math.h */
{ "acos", 1, 0, SQLITE_UTF8, 0, acosFunc },
{ "asin", 1, 0, SQLITE_UTF8, 0, asinFunc },
{ "atan", 1, 0, SQLITE_UTF8, 0, atanFunc },
{ "atn2", 2, 0, SQLITE_UTF8, 0, atn2Func },
/* XXX alias */
{ "atan2", 2, 0, SQLITE_UTF8, 0, atn2Func },
{ "acosh", 1, 0, SQLITE_UTF8, 0, acoshFunc },
{ "asinh", 1, 0, SQLITE_UTF8, 0, asinhFunc },
{ "atanh", 1, 0, SQLITE_UTF8, 0, atanhFunc },
{ "difference", 2, 0, SQLITE_UTF8, 0, differenceFunc},
{ "degrees", 1, 0, SQLITE_UTF8, 0, rad2degFunc },
{ "radians", 1, 0, SQLITE_UTF8, 0, deg2radFunc },
{ "cos", 1, 0, SQLITE_UTF8, 0, cosFunc },
{ "sin", 1, 0, SQLITE_UTF8, 0, sinFunc },
{ "tan", 1, 0, SQLITE_UTF8, 0, tanFunc },
{ "cot", 1, 0, SQLITE_UTF8, 0, cotFunc },
{ "cosh", 1, 0, SQLITE_UTF8, 0, coshFunc },
{ "sinh", 1, 0, SQLITE_UTF8, 0, sinhFunc },
{ "tanh", 1, 0, SQLITE_UTF8, 0, tanhFunc },
{ "coth", 1, 0, SQLITE_UTF8, 0, cothFunc },
{ "exp", 1, 0, SQLITE_UTF8, 0, expFunc },
{ "log", 1, 0, SQLITE_UTF8, 0, logFunc },
{ "log10", 1, 0, SQLITE_UTF8, 0, log10Func },
{ "power", 2, 0, SQLITE_UTF8, 0, powerFunc },
{ "sign", 1, 0, SQLITE_UTF8, 0, signFunc },
{ "sqrt", 1, 0, SQLITE_UTF8, 0, sqrtFunc },
{ "square", 1, 0, SQLITE_UTF8, 0, squareFunc },
{ "ceil", 1, 0, SQLITE_UTF8, 0, ceilFunc },
{ "floor", 1, 0, SQLITE_UTF8, 0, floorFunc },
{ "pi", 0, 0, SQLITE_UTF8, 1, piFunc },
/* string */
{ "replicate", 2, 0, SQLITE_UTF8, 0, replicateFunc },
{ "charindex", 2, 0, SQLITE_UTF8, 0, charindexFunc },
{ "charindex", 3, 0, SQLITE_UTF8, 0, charindexFunc },
{ "leftstr", 2, 0, SQLITE_UTF8, 0, leftFunc },
{ "rightstr", 2, 0, SQLITE_UTF8, 0, rightFunc },
{ "ltrim", 1, 0, SQLITE_UTF8, 0, ltrimFunc },
{ "rtrim", 1, 0, SQLITE_UTF8, 0, rtrimFunc },
{ "trim", 1, 0, SQLITE_UTF8, 0, trimFunc },
{ "replace", 3, 0, SQLITE_UTF8, 0, replaceFunc },
{ "reverse", 1, 0, SQLITE_UTF8, 0, reverseFunc },
{ "proper", 1, 0, SQLITE_UTF8, 0, properFunc },
{ "padl", 2, 0, SQLITE_UTF8, 0, padlFunc },
{ "padr", 2, 0, SQLITE_UTF8, 0, padrFunc },
{ "padc", 2, 0, SQLITE_UTF8, 0, padcFunc },
{ "strfilter", 2, 0, SQLITE_UTF8, 0, strfilterFunc },
};
/* Aggregate functions */
static const struct {
char *zName;
signed char nArg;
u8 argType;
u8 needCollSeq;
void (*xStep)(sqlite3_context*,int,sqlite3_value**);
void (*xFinalize)(sqlite3_context*);
} aAggs[] = {
{ "stdev", 1, 0, 0, varianceStep, stdevFinalize },
{ "variance", 1, 0, 0, varianceStep, varianceFinalize },
{ "mode", 1, 0, 0, modeStep, modeFinalize },
{ "median", 1, 0, 0, modeStep, medianFinalize },
{ "lower_quartile", 1, 0, 0, modeStep, lower_quartileFinalize },
{ "upper_quartile", 1, 0, 0, modeStep, upper_quartileFinalize },
};
http://www.nabble.com/file/6285/SQLite.zip SQLite.zip
--
View this message in context:
http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8833684
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
