Using SQL Functions

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)} = ""

Function
Desciption

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 character t 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 )}
Http://www.minisoft.com/pages/middleware/odbc32/pages/odbcae.htm