Login | Register
My pages Projects Community openCollabNet

Axion: Functions

Axion does not limit the user to a set of predefined functions. Instead, the user can easily plug in new functions and extend or change the names of registered functions. Any class that implements org.axiondb.AggregateFunction or ScalarFunction and for which there is a corresponding FunctionFactory can be dropped right into Axion via a configuration file. The org.axiondb.functions package has quite a few examples.

The mappings between registered functions and the associated Java classes is defined in the axiondb.properties file.

By default, the following functions are registered:

Name Description

Arithmetic Operators

 

+ - * /

Add, Subtract, Multiply, Divide

||

Concat function.Concatenates two strings.

   

Relational Operators

 

==, !=, >, >=, <, <=

Equal, NotEqual, GreaterThan, GreaterThanOrEqual, LesThan, LessThanOrEqual

   

Logical Operators

 

isnull, isnotnull

Checks whether a value is (is not) null.

and, or, not,

And, or, not

in, notin

In, NotIn

exists, notexists

Exists, NotExists

   
   

Mathematical functions

 

abs(x)

returns the absolute value. abs(-5) = 5

mod(x,y)

returns the modulo (remainder of an integer division). mod(8,3) = 2

round(x[,y])

rounds x to y digits after the decimal point (default: 0 digits). round(5.5) = 6), round(5.831,2) = 5.83

sign(x)

returns -1 if value is negative, 0 if value is 0, 1 if value is greater than zero. sign(12) = 1, sign(-3) = -1, sign(0) = 0

truncate(x,y)

truncates a to b digits after the decimal point. truncate(7.9,0) = 7, truncate(-7.093,2) = -7.09

bitand(x,y)

returns x & y

bitor

returns x | y

log10(x)

returns the logarithm to the base of 10

   

String functions

 

ascii(s)

returns the ASCII code of the leftmost character of s. ascii('Axion') = 65

length(s)

returns the number of characters in s. length('Axion') = 5

replace(s,old,new)

replaces all occurences of old in s with new. replace('nena','n','k')='keka'

instr(s,sub[,pos])

returns the position of the first occurrence of sub in s beginning after pos (pos defaults to 0). If pos is negative searches from right to left. instr('sanjehthjethjeth','je',-6) = 8

difference(x,y)

returns the difference between the sound of x and the sound of y (based on the soundex algorithm). difference('smothers','brothers') = 2

space(x)

returns a string consisting of x spaces. space(4) = ' '

substring(str,m[,n])

Returns substring of str, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, it counts from the beginning of str to find the first character. If m is negative, it counts backwards from the end of str. If n is omitted, it returns all characters to the end of str. If n is less than 1, a null is returned. Floating-point numbers passed as arguments to substr are automatically converted to integers. If m greater than length of str, return null.If m+n is bigger than length of str, ignore n. if str is null, return null. substring('duvuru',3,2) = 'vu'

lpad(s,count,pad)

Left pads the string s with occurences of pad so that the length of the result becomes count. lpad('foo',7,'a') = 'aaaafoo'

rpad(s,count,pad)

Right pads the string s with occurences of pad so that the length of the result becomes count. rpad('foo',7,'a') = 'fooaaaa'

ltrim(s)

Removes leading spaces from s. ltrim(' foo') = 'foo'

rtrim(s)

Removes trailing spaces from s. rtrim('foo ') = 'foo'

concat(s1,s2)

Concatenates s1 and s2. See also || operator. concat('hello ','world') = 'hello world'

lower, lcase(s)

Converts s to lower case. lower('AXION') = axion

upper, ucase(s)

Converts s to upper case. upper('axion') = AXION

base64decode(s)

Decodes a string that was encoded in Base64

base64encode(s)

Encodes a string in Base64

char(x)

Converts an ASCII value into a character. char(65)='A'

contains(s,sub)

Returns true if the substring sub is contained in the string s, false otherwise. contains('Axion','io') = true

like2regexp

An implementation of the SQL like function

matches(s1,s2)

Returns a boolean valuethat indicates whether first string matches the regular expression represented by the second string. matches('hE', '[hH][eE]') = true

coalesce(x,y [,z...])

Returns the first non-null value from the list. Coalesce(null,"hello", "world") = "hello"

   

Other functions

 

now

Returns a new timestamp with the current date and time.

datediff(intervalltype, timestamp1, timestamp2) (since 1.0RC3)

Returns integer difference (timestamp2 - timestamp1) in units of interval of type interval_type. Valid interval types (org.axiondb.types.TimestampType): 1=MILLISECOND, 2=SECOND, 4=MINUTE, 8=HOUR, 16=DAY, 32=WEEK, 64=MONTH, 128=QUARTER, 256=YEAR.

dateadd(intervalltype, intervall, timestamp)(since 1.0RC3)

Returns new timestamp which is (timestamp + interval) where interval is integer units of interval_type. Valid interval types (org.axiondb.types.TimestampType): 1=MILLISECOND, 2=SECOND, 4=MINUTE, 8=HOUR, 16=DAY, 32=WEEK, 64=MONTH, 128=QUARTER, 256=YEAR.

rownum

Returns the number of the currently active row.

ifthen(condition, value)

Returns value if condition evalates to true; else, it returns null.

   

Aggregate Functions

 

avg

Returns the average of all values in a set

sum

Returns the sum of all values in a set

count

Counts the number of values in a set

max

Returns the maximum value in a set

min

Returns the minimum value in a set



Axion - Open Source Java Database Engine
$Id: functions.html,v 1.9 2007/11/15 15:09:27 rwald Exp $
Published 15 Nov 2007 at 3:07 PM GMT.