The FairCom DB conditional expression parser has numerous builtin functions for advanced conditional filtering possibilities. These are arranged and described in the following categories:
These builtin functions closely follow C library functions.
Function 
Explanation 

int atoi( char* String ) 
convert string to integer 
int atol( char* String ) 
convert string to long 
double atof( char* String ) 
convert string to double 
int cabs( into Value ) 
return the absolute value of a complex number. 
int labs( into Value ) 
return the absolute value of a long integer. 
double fabs( double Value ) 
return the absolute value of a double 
double ceil( double Value ) 
return the ceiling of a value 
double floor( double Value ) 
return the floor of a value 
double fmod( double r, double t ) 
return the remainder of r by t as a double 
int strlen( char* String ) 
return the length of a string 
int strcmp( char* s, char* t ) 
compare two strings 
int stricmp( char* s, char* t ) 
compare strings without regard to case 
int strncmp( char* s, char* t, int len) 
compare characters of two strings 
int strnicmp( char* s, char *t, int len ) 
compare characters of two strings without regard to case 
int memcmp(char* s, char *d, int len) 
compare bytes of two memory locations 
u_strcmp 
compare Unicode strings: Int u_strcmp (UChar* s1, UChar* s2) 
Function 
Return Type 
Description 

ASCII ( char_expression ) 
int 
Returns the ASCII value of the first character of char_expression.

CHR ( integer_expression ) 
text 
Returns a character string with the first character having an ASCII value equal to the argument expression.

CONCAT ( char_expression1 ,char_expression2 ) 
text 
Returns a concatenated character string formed by concatenating char_expression1 with char_expression2.

FIELD ( offset, size ) 
text 
Returns the content of record at offset with length size as if it were a CT_ARRAY field. 
INSERT ( char_expression1, start, length, char_expression2 ) 
text 
Returns a character string where length characters have been deleted from char_expr1 beginning at start and char_expr2 has been inserted into char_expr1, beginning at start.

LEFT ( char_expression, count ) 
text 
Returns the leftmost count of characters of char_expression.

LENGTH ( char_expression ) 
int 
Returns the number of characters in char_expression.

LOCATE ( charexpr1, charexpr2 [ , start ] ) 
int 
Returns the location of the first occurrence of charexpr2 in charexpr1. If the function includes an optional integer argument start LOCATE begins searching charexpr1 at that position. If the function omits start, LOCATE begins its search at the beginning of charexpr1. LOCATE denotes the first character position of a character expression as 1. If the search fails, LOCATE returns 0.

LOWER ( char_expression ) 
text 
Returns the result of the character expression after converting all characters to lowercase.

LPAD ( char_expression, length [ , pad_expression ] ) 
text 
Returns char_expression padded with optional pad_expression until the length of the resulting string is equal to argument length.

LTRIM ( char_expression [, char_set ] ) 
text 
Removes leading characters in char_expression, present in char_set and returns the resultant string. Thus, the first character in the result is guaranteed not in char_set. If char_set is omitted, the function removes leading and trailing spaces from char_expression.

MATCH ( char_expression, pattern_expression ) 
int 
Compares char_expression to pattern_expression. MATCH returns 1 if the expression matches the pattern and zero if the expression does not match.

MATCHI ( char_expression, pattern_expression ) 
int 
Compares char_expression to pattern_expression as a caseinsensitive comparison. MATCHI returns 1 if the expression matches the pattern and zero if the expression does not match.

MEMCMP ( char_expression1, char_expression2, count ) 
int 
Compares count bytes of char_expression1 and char_expression2 and returns zero if both strings are equal, a negative value if char_expression1 is less than char_expression2 , or a positive value if char_expression1 is greater than char_expression2.

REPEAT ( char_expression, count ) 
int 
Returns a character string composed of char_expression repeated count times.

RIGHT ( char_expression, count ) 
text 
Returns rightmost count of characters from char_expression.

ROWID (0, 0) 
double 
Returns the numeric row ID value if available. 
RPAD ( char_expression, length [ , pad_expression ] ) 
text 
Returns char_expression padded with pad_expression such that after padding, length of result equals length.

RTRIM ( char_expression [, char_set ] ) 
text 
Removes trailing characters in char_expression present in char_set and returns the resultant string. Thus, the last character in the result is guaranteed not in char_set. If char_set is omitted, the function removes trailing spaces from char_expression.

SPACE ( expression ) 
text 
Returns a character string consisting of a number of spaces specified by expression.

SUBSTRING ( char_expression, start [, length ] ) 
int 
Returns the substring of char_expression beginning at start and length characters long. If length is not specified, substring starting at start up to the end of char_expression is returned.

UPPER ( char_expression ) 
text 
Returns char_expression after converting all characters to uppercase.

Function 
Return Type 
Description 

ADD_DAYS ( date_expression, integer_expression ) 
DATE 
Adds to the specified date_expression value the specified number of integer_expression days and returns the resultant date value.

ADD_MONTHS ( date_expression, integer_expression ) 
DATE 
Adds to the specified date_expression value the number of integer_expression months and returns the resultant date value.

CURDATE ( ) 
DATE 
Returns the current date as a DATE value. This function takes no arguments. 
CURTIME ( ) 
TIME 
Returns the current time as a TIME value. This function takes no arguments. 
DAYNAME ( date_expression ) 
text 
Returns a character string for the day name portion of date_expression. date_expression can be the name of a column, the result of another scalar function, or a DATE or TIMESTAMP literal. The return value is one of SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY or SATURDAY. 
DAYOFMONTH ( date_expression ) 
int 
Returns the day of month of date_expression as a short integer value in the range 1  31.

DAYOFWEEK ( date_expression ) 
int 
Returns the day of week of date_expression as an integer value in the range of 1 – 7, where 1 is Sunday, 2 is Monday, etc.

DAYOFYEAR ( date_expression ) 
int 
Returns the day of year of date_expression as an integer value in the range 1  366.

HOUR ( time_expression ) 
int 
Returns the hour of time_expression as an integer value in the range 0  23.

LAST_DAY ( date_expression ) 
DATE 
Returns the date corresponding to the last day of the month containing date_expression.

MINUTE ( time_expression ) 
int 
Returns the minute value of time_expression as an integer in the range 0  59.

MONTH ( date_expression ) 
int 
Returns the month number of date_expression as an integer value in the range 1  12.

MONTHNAME ( date_expression ) 
text 
Returns the name of the month (for example, JANUARY, through DECEMBER) for the month portion of date_expression.

NOW () 
TIMESTAMP 
Returns the current date and time as a TIMESTAMP value. This function takes no arguments. 
QUARTER ( date_expression ) 
int 
Returns the quarter of date_expression as an integer value in the range 1  4.

SECOND ( time_expression ) 
int 
Returns the seconds time_expression as an integer value in the range 0  59.

SYSDATE () 
DATE 
Returns the current date as a DATE value. This function takes no arguments, and trailing parentheses are optional. This function is similar to CURDATE. 
SYSTIME () 
TIME 
Returns the current time as a TIME value. This function takes no arguments, and trailing parentheses are optional. This function is similar to CURTIME. 
SYSTIMESTAMP () 
TIMESTAMP 
Returns the current date and time as a TIMESTAMP value. This function takes no arguments, and trailing parentheses are optional. 
TIMET2CTDATE 
CTDATE 
convert integer Unix time_t value to a ctree Date type (CTDATE) 
TIMET2CTTIME 
CTTIME 
convert integer Unix time_t value to a ctree Time type (CTTIME) 
TIMET2CTTIMES 
CTTIMES 
convert integer Unix time_t value to a ctree Timestamp type (CTTIMES) 
WEEK ( date_expression ) 
int 
Returns the week of date_expression as an integer value in the range 1  53.

YEAR ( date_expression ) 
int 
Returns the year of date_expression as an integer value in the range 0  9999.

Function 
Return Type 
Description 

ABS ( expression ) 
int 
Computes absolute value of expression.

ACOS ( expression ) 
double 
Returns arccosine of expression. ACOS takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse. The result is expressed in radians and is in the range PI/2 to PI/2 radians. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.

ASIN ( expression ) 
double 
Reurns the arcsine of expression. ASIN takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse. The result is expressed in radians and is in the range PI/2 to PI/2 radians. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.

ATAN ( expression ) 
double 
Returns the arctangent of expression. ATAN takes the ratio (expression) of two sides of a right triangle and returns the corresponding angle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle. The result is expressed in radians and is in the range PI/2 to PI/2 radians. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.

CABS ( expression ) 
int 
Calculates the absolute value of a complex number. 
CEIL ( expression ) 
double 
Returns the smallest integer greater than or equal to expression.

CEILING ( expression ) 
double 
Returns the smallest integer greater than or equal to expression.

COS ( expression ) 
double 
Returns the cosine of expression. COS takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.

EXP ( expression ) 
double 
Returns the exponential value of expression (e raised to the power of expression).

FABS ( expression ) 
double 
Returns the absolute value of expression.

FLOOR ( expression ) 
int 
Returns the largest integer less than or equal to expression.

FMOD ( expression1, expression2 ) 
double 
Calculates expression1 modulo expression2, the remainder of expression1 / expression2.

LABS ( expression ) 
integer 
Computes the absolute value of an INTEGER value.

LOG10( expression ) 
double 
Returns the base 10 logarithm of expression.

MOD ( expression1, expression2 ) 
int 
Returns the remainder of expression1 divided by expression2.

PI ( ) 
number 
Returns the constant value of pi as an approximated numeric value. This function takes no arguments. 
POWER( expression1, expression2 ) 
double 
Returns expression1 raised to the power of expression2.

RAND( [ expression ] ) 
int 
Returns a randomlygenerated number, using expression as an optional seed value.

SIGN ( expression ) 
int 
Returns: +1 if expression > 0 1 if expression < 0 0 if expression = 0

SIN ( expression ) 
double 
SIN takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse. To convert degrees to radians, multiply degrees by PI/180. To convert radians to degrees, multiply radians by 180/PI.

SQRT ( expression ) 
double 
SQRT return the square root of expression.

TAN ( expression ) 
number 
TAN returns the tangent of expression. TAN takes an angle (expression) and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle. To convert degrees to radians, multiply degrees by Pi/180. To convert radians to degrees, multiply radians by 180/Pi.

The filter expression parser allows you to use explicit type casts in expressions. This is very useful if you are comparing fields of different types and want to control the result of an expression.
For example, suppose "Salary" is a CT_MONEY field and "Average" is a CT_DFLOAT field; type casts can be used as illustrated in the following expression: (Salary  (int)Average) > 500
The following type casts may be used in conditional expressions:
You cannot type cast a string expression.
When mixing different types in an expression without explicit type casting, the conditional expression parser automatically promotes the types using the following rule:
In the great majority of cases, mixing strings with numeric values returns a parsing error.
The following operators are allowed in filters and conditional expressions.
Mathematical Operators
+ 
Adds two operands 
 
Subtracts two operands or negates an operand (e.g., 5) 
* 
Multiplication 
/ 
Division 
% 
Modulus 
Relational Operators
== 
Equal to 
!= 
Not equal to 
< 
Less than 
<= 
Less or equal to 
> 
Greater than 
>= 
Greater than or equal to 
Logical Operators
&& 
And 
 
Or 
! 
Not 
Binary Operators
& 
And 
 
Or 
~ 
Not 
^ 
Xor 
NULL Operators
IS NULL 
IS NOT NULL 