(ODBC) 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 | |
| ISNULL | Returns true if the value is NULL. {fn ISNULL(value)} |
| ISNUMERIC | Returns true if the value represents a number. {fn ISNUMERIC(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)} |