Product Documentation

Conditional Expressions

Previous Topic

Next Topic

Predefined Functions

The FairCom DB conditional expression parser has numerous built-in functions for advanced conditional filtering possibilities. These are arranged and described in the following categories:

In This Section

C Language Equivalents

String and Text Functions

Date and Time Functions

Mathematical Functions

Previous Topic

Next Topic

C Language Equivalents

These built-in 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)

Previous Topic

Next Topic

String and Text Functions

Function

Return Type

Description

ASCII ( char_expression )

int

Returns the ASCII value of the first character of char_expression.

  • char_expression must be a character type value.
  • If char_expression is null, result is NULL.

CHR ( integer_expression )

text

Returns a character string with the first character having an ASCII value equal to the argument expression.

  • integer_expression must evaluate to INTEGER, SMALLINT or TINYINT data type.
  • If integer_expression is NULL CHR returns NULL.

CONCAT ( char_expression1 ,char_expression2 )

text

Returns a concatenated character string formed by concatenating char_expression1 with char_expression2.

  • Both arguments must evaluate to Character type.
  • If either expression is null, result is NULL.

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.

  • char_expr1 and char_expr2 must evaluate to a character value
  • start and length must evaluate to INTEGER values
  • If either expression is null result is NULL

LEFT ( char_expression, count )

text

Returns the leftmost count of characters of char_expression.

  • char_expression must evaluate to a CHAR value.
  • count must evaluate to an INTEGER value.
  • If count is less than one, an empty string is returned.
  • If count is greater than the length of char_expression, char_expression is returned.
  • If char_expression is null, LEFT returns NULL.

LENGTH ( char_expression )

int

Returns the number of characters in char_expression.

  • char_expression must evaluate to a CHAR value.
  • If char_expression is null LENGTH returns NULL.

LOCATE ( char-expr1, char-expr2 [ , start ] )

int

Returns the location of the first occurrence of char-expr2 in char-expr1. If the function includes an optional integer argument start LOCATE begins searching char-expr1 at that position. If the function omits start, LOCATE begins its search at the beginning of char-expr1. LOCATE denotes the first character position of a character expression as 1. If the search fails, LOCATE returns 0.

  • char_expr1 and char_expr2 must evaluate to a CHAR value.
  • start, if supplied, must evaluate to an INTEGER value.
  • If either expression is null, LOCATE returns NULL.

LOWER ( char_expression )

text

Returns the result of the character expression after converting all characters to lowercase.

  • char_expression must evaluate to a CHAR value.
  • If char_expression is null LOWER returns NULL.

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.

  • char_expression must evaluate to a CHAR value.
  • length must evaluate to an INTEGER value.
  • pad_expression, if specified, must evaluate to a CHAR value.
  • if pad_expression is not specified, spaces are used for padding.
  • If any expression is null LPAD returns NULL.

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.

  • char_expression must evaluate to a CHAR value.
  • char_set, if specified, must evaluate to a CHAR value.

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.

  • char_expression and pattern_expression must evaluate to CHAR values.
  • Trailing blanks are significant.
  • If any expression is null MATCH returns NULL.

    The following special characters can be used in pattern_expression:

  • * (asterisk) : multi-character wildcard
  • . (period) : single-character wildcard
  • ~ (tilde) : escape character

MATCHI ( char_expression, pattern_expression )

int

Compares char_expression to pattern_expression as a case-insensitive comparison. MATCHI returns 1 if the expression matches the pattern and zero if the expression does not match.

  • char_expression and pattern_expression must evaluate to CHAR values.
  • Trailing blanks are significant.
  • If either expression is null MATCHI returns NULL.

    The following special characters can be used in pattern_expression:

  • * (asterisk) : multi-character wildcard
  • . (period) : single-character wildcard
  • ~ (tilde) : escape character

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.

  • char_expression1 and char_expression2 must evaluate to CHAR values.
  • count must evaluate to an INTEGER value.
  • If any argument is null MEMCMP returns NULL.

REPEAT ( char_expression, count )

int

Returns a character string composed of char_expression repeated count times.

  • char_expr must evaluate to a CHAR value.
  • count must evaluate to an exact numeric value.
  • If either argument is null REPEAT returns NULL.

RIGHT ( char_expression, count )

text

Returns rightmost count of characters from char_expression.

  • char_expression must evaluate to a CHAR expression.
  • count must evaluate to an exact numeric value.
  • If either argument is null RIGHT returns NULL.

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.

  • char_expression must evaluate to a CHAR value.
  • length must evaluate to an exact numeric value.
  • pad_expression, if specified, must evaluate to a CHAR value.
  • If pad_expression is not specified, paces are used for padding.
  • If any argument is null RPAD returns NULL.

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.

  • char_expression must evaluate to a CHAR value.
  • char_set, if specified, must evaluate to a CHAR value.
  • If either argument is null RTRIM returns NULL.

SPACE ( expression )

text

Returns a character string consisting of a number of spaces specified by expression.

  • expression must evaluate to an exact numeric value.
  • If expression is null SPACE returns NULL.

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.

  • char_expression must evaluate to a CHAR value.
  • start must evaluate to an exact numeric value.
  • The first character position in char_expression is 1.
  • length, if specified, must evaluate to an exact numeric value.
  • If any argument is null SUBSTR returns NULL.

UPPER ( char_expression )

text

Returns char_expression after converting all characters to uppercase.

  • char_expression must evaluate to a CHAR value.
  • If char_expression is null UPPER returns NULL.

Previous Topic

Next Topic

Date and Time Functions

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.

  • date_expression must be a DATE type value.
  • integer_expression must be a numeric value.
  • If either expression is NULL, result is NULL.

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.

  • date_expression must be a DATE type value.
  • integer_expression must be a numeric value.
  • If either expression is NULL, result is NULL.

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.

  • The argument to the function must evaluate to a DATE type value.
  • If date_expression is null DAYOFMONTH returns null.

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.

  • date_expression must evaluate to a DATE type value.
  • If date_expression is null DAYOFWEEK returns null.

DAYOFYEAR ( date_expression )

int

Returns the day of year of date_expression as an integer value in the range 1 - 366.

  • date_expression must evaluate to a DATE type value.
  • If date_expression is null DAYOFYEAR returns null.

HOUR ( time_expression )

int

Returns the hour of time_expression as an integer value in the range 0 - 23.

  • time_expression must evaluate to a TIME value.
  • If time_expression is null HOUR returns null.

LAST_DAY ( date_expression )

DATE

Returns the date corresponding to the last day of the month containing date_expression.

  • date_expression must evaluate to a DATE value.
  • If date_expression is null LAST_DAY returns null.

MINUTE ( time_expression )

int

Returns the minute value of time_expression as an integer in the range 0 - 59.

  • time_expression must evaluate to a TIME value.
  • If time_expression is null MINUTE return null.

MONTH ( date_expression )

int

Returns the month number of date_expression as an integer value in the range 1 - 12.

  • date_expression must evaluate to a DATE value.
  • If date_expression is null, MONTH returns null.

MONTHNAME ( date_expression )

text

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

  • date_expression must evaluate to a DATE value.
  • If date_expression is null MONTHNAME returns null.

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.

  • date_expression must evaluate to a DATE value.
  • If date_expression is null QUARTER returns null.

SECOND ( time_expression )

int

Returns the seconds time_expression as an integer value in the range 0 - 59.

  • time_expression must evaluate to a TIME value.
  • If time_expression is null SECOND return null.

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 c-tree Date type (CTDATE)

TIMET2CTTIME

CTTIME

convert integer Unix time_t value to a c-tree Time type (CTTIME)

TIMET2CTTIMES

CTTIMES

convert integer Unix time_t value to a c-tree Timestamp type (CTTIMES)

WEEK ( date_expression )

int

Returns the week of date_expression as an integer value in the range 1 - 53.

  • date_expression must evaluate to a DATE value.
  • If date_expression is null WEEK returns null.

YEAR ( date_expression )

int

Returns the year of date_expression as an integer value in the range 0 - 9999.

  • date_expression must evaluate to a DATE value
  • If date_expression is null YEAR return null

Previous Topic

Next Topic

Mathematical Functions

Function

Return Type

Description

ABS ( expression )

int

Computes absolute value of expression.

  • The argument to the function must be of type TINYINT, SMALLINT, INTEGER, NUMERIC, REAL or FLOAT.
  • If expression evaluates to null, the result is null.

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.

  • expression must be in the range -1 to 1.
  • expression must evaluate to an approximate numeric data type.

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.

  • expression must be in the range -1 to 1.
  • expression must evaluate to an approximate numeric data type.

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.

  • expression must be in the range -1 to 1.
  • expression must evaluate to an approximate numeric data type.

CABS ( expression )

int

Calculates the absolute value of a complex number.

CEIL ( expression )

double

Returns the smallest integer greater than or equal to expression.

  • expression must evaluate to a numeric type.
  • CEIL is similar to CEILING function.

CEILING ( expression )

double

Returns the smallest integer greater than or equal to expression.

  • expression must evaluate to a numeric type.
  • CEILING is similar to CEIL function.

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.

  • expression specifies an angle in radians.
  • expression must evaluate to an approximate numeric data type.

EXP ( expression )

double

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

  • expression must evaluate to an approximate numeric data type.
  • If expression is null EXP returns null.

FABS ( expression )

double

Returns the absolute value of expression.

  • expression must evaluate to a numeric value.
  • If expression is null FABS returns null.

FLOOR ( expression )

int

Returns the largest integer less than or equal to expression.

  • expression must evaluate to a numeric value.
  • If expression is null FLOOR returns null.

FMOD ( expression1, expression2 )

double

Calculates expression1 modulo expression2, the remainder of expression1 / expression2.

  • Both expressions must evaluate to a numeric value.
  • If either expression is null FMOD returns null.

LABS ( expression )

integer

Computes the absolute value of an INTEGER value.

  • expression must evaluate to an INTEGER value.
  • If expression is null LABS returns null.

LOG10( expression )

double

Returns the base 10 logarithm of expression.

  • expression must evaluate to a NUMERIC value.
  • if expression is null LOG10 returnr null.

MOD ( expression1, expression2 )

int

Returns the remainder of expression1 divided by expression2.

  • expression1 and expression2 must evaluate to exact numeric values.
  • If expression2 evaluate to zero, a runtime error will be generated, which will cause the expression being evaluated as false.
  • If either expression is null MOD returns null.

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.

  • Both expression1 and expression2 must evaluate to numeric values.
  • If either expression is null POWER returns null.

RAND( [ expression ] )

int

Returns a randomly-generated number, using expression as an optional seed value.

  • expression, if specified, must evaluate to an exact numeric value.
  • If expression is specified and it is null RAND returns null.

SIGN ( expression )

int

Returns:

+1 if expression > 0

-1 if expression < 0

0 if expression = 0

  • expression must evaluate to a numeric value.
  • If expression is null SIGN returns null.

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.

  • expression must evaluate to a numeric value.
  • If expression is null SIN returns null.

SQRT ( expression )

double

SQRT return the square root of expression.

  • expression must evaluate to a numeric value.
  • If expression is null SQRT returns null.

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.

  • expression must evaluate to a numeric value.
  • If expression is null TAN returns null.

TOCIndex