| Products | Minisoft News | Events | Downloads | Product Manuals | 
 

Using SQL Functions

Function must be entered in escaped ODBC syntax. The functions are entered in the form
{fn function([parm_1[,parm_n]])}.

For example:

select LAST_NAME, FIRST_NAME,
{fn CONCAT(FIRST_NAME,{fn CONCAT(" ",LAST_NAME)})}
from CUSTOMER
where {fn LEFT(LAST_NAME,1)}="A"
Function Description
String Functions
ASCII Returns the ASCII code value of the leftmost character of string_exp as an integer.
{fn ASCII(string_exp)}
CHAR Returns a Character from the value of code (0 to 255).
{fn CHAR(code)}
CHAR_LENGTH Returns the Length of the Character String.
{fn CHAR_LENGTH(string_exp)}
CHARACTER_LENGTH Returns the Length of the Character String.
{fn CHARACTER_LENGTH(string_exp)}
CONCAT Returns a Character String that consists of the two strings passed.
{fn CONCAT(string_exp1, string_exp2)}
INSERT Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp1, beginning at start.
{fn INSERT(string_exp1, start, length, string_exp2)}
LCASE Returns a string consisting only of lower case characters.
{fn LCASE(string_exp)}
LEFT Returns the number of characters requested from the left side of the given string.
{fn LEFT(string_exp,count)}
LENGTH Returns the Length of the Character String.
{fn LENGTH(string_exp)}
LOCATE Returns the position of a substring within a string.
{fn LEFT (string_exp1,string_exp2[,start])}
LTRIM Returns a Character String except for any spaces on the left.
{fn LTRIM(string_exp)}
OCTET_LENGTH Returns the Length in bytes of the value.
{fn OCTET_LENGTH(string_exp)}
REPEAT Returns a given character the requested number of times.
{fn REPEAT(string_exp,count)}
REPLACE Search str_exp1 for occurrences of str_exp2 and replace with str_exp3.
{fn LTRIM(str_exp1,str_exp2,str_exp3)}
RIGHT Returns the rightmost count characters of string_exp. Returns the number of characters requested from the left side of the given string.
{fn RIGHT(string_exp,count)}
RTRIM Returns the characters of string_exp with trailing blanks removed.
{fn LTRIM(string_exp)}
SPACE Returns a character string consisting of count spaces. Returns the number of characters requested from the left side of the given string.
{fn SPACE(count)}
SUBSTRING Extracts one or more characters from a string. Returns the number of characters requested from the left side of the given string.
{fn SUBSTRING(string_exp,start,length)}
UCASE Converts strings to uppercase. Returns the number of characters requested from the left side of the given string.
{fn UCASE(string_exp)}
Numeric Functions
ABS Returns the absolute value of numeric_exp.
{fn ABS(numeric_exp)}
CEILING Returns the smallest integer greater than or equal to numeric_exp. The return value is of the same data type as the input parameter.
{fn CEILING(numeric_exp)}
FLOOR Rounds a number down to the nearest (smallest) whole number.
{fn FLOOR(numeric_exp)}
MOD Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.
{fn MOD(integer_exp1,integer_exp2)}
ROUND Rounds a number (value1) down to the number of decimal digits specified in value2.
{fn ROUND(value1,value2)}
SIGN Returns a value indicating the sign of the provided value.
{fn SIGN(value)}
Date Functions
CURRENT_DATE Returns the current host system date.
{fn CURRENT_DATE( )}
CURDATE Returns the current host system date.
{fn CURDATE( )}
DAYOFMONTH Returns a number that consists of the Day portion of a given date.
{fn DAYOFMONTH(date_exp)}
MONTH Returns a number that consists of the Month portion of a given date. Returns a number that consists of the Day portion of a given date.
{fn MONTH(date_exp)}
YEAR Returns a number that consists of the Year portion of a given date. Returns a number that consists of the Day portion of a given date.
{fn YEAR(date_exp)}
Misc Functions
IS_NULL Returns true if the value is NULL.
{fn IS_NULL(value)}
IS_NUMERIC Returns true if the value represents a number.
{fn IS_NUMERIC(value)}
COALESCE Returns the first non-null value from the list provided. Used in joins that can return NULL values.
{fn COALESCE(value1,value2)}
DECODE Provides an IF THEN ELSE structure in the form,
if (column==test)then value1 else value2.
{fn DECODE(column,test,value1,value2)}