{"id":2342,"date":"2014-12-18T20:16:44","date_gmt":"2014-12-18T20:16:44","guid":{"rendered":"https:\/\/alt2.minisoft.com\/support\/?p=2342"},"modified":"2018-01-03T21:09:58","modified_gmt":"2018-01-03T21:09:58","slug":"using-sql-functions","status":"publish","type":"post","link":"https:\/\/email.minisoft.com\/support\/using-sql-functions\/","title":{"rendered":"(ODBC) Using SQL Functions"},"content":{"rendered":"

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

For example:<\/p>\n

select LAST_NAME, FIRST_NAME,
\n{fn CONCAT(FIRST_NAME,{fn CONCAT(” “,LAST_NAME)})}
\nfrom CUSTOMER
\nwhere {fn LEFT(LAST_NAME,1)}=”A”<\/p><\/blockquote>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function<\/td>\nDescription<\/td>\n<\/tr>\n
String Functions<\/th>\n<\/tr>\n
ASCII<\/td>\nReturns the ASCII code value of the leftmost character of string_exp as an integer.
\n{fn ASCII(string_exp)}<\/td>\n<\/tr>\n
CHAR<\/td>\nReturns a Character from the value of code (0 to 255).
\n{fn CHAR(code)}<\/td>\n<\/tr>\n
CHAR_LENGTH<\/td>\nReturns the Length of the Character String.
\n{fn CHAR_LENGTH(string_exp)}<\/td>\n<\/tr>\n
CHARACTER_LENGTH<\/td>\nReturns the Length of the Character String.
\n{fn CHARACTER_LENGTH(string_exp)}<\/td>\n<\/tr>\n
CONCAT<\/td>\nReturns a Character String that consists of the two strings passed.
\n{fn CONCAT(string_exp1, string_exp2)}<\/td>\n<\/tr>\n
INSERT<\/td>\nReturns 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.
\n{fn INSERT(string_exp1, start, length, string_exp2)}<\/td>\n<\/tr>\n
LCASE<\/td>\nReturns a string consisting only of lower case characters.
\n{fn LCASE(string_exp)}<\/td>\n<\/tr>\n
LEFT<\/td>\nReturns the number of characters requested from the left side of the given string.
\n{fn LEFT(string_exp,count)}<\/td>\n<\/tr>\n
LENGTH<\/td>\nReturns the Length of the Character String.
\n{fn LENGTH(string_exp)}<\/td>\n<\/tr>\n
LOCATE<\/td>\nReturns the position of a substring within a string.
\n{fn LEFT (string_exp1,string_exp2[,start])}<\/td>\n<\/tr>\n
LTRIM<\/td>\nReturns a Character String except for any spaces on the left.
\n{fn LTRIM(string_exp)}<\/td>\n<\/tr>\n
OCTET_LENGTH<\/td>\nReturns the Length in bytes of the value.
\n{fn OCTET_LENGTH(string_exp)}<\/td>\n<\/tr>\n
REPEAT<\/td>\nReturns a given character the requested number of times.
\n{fn REPEAT(string_exp,count)}<\/td>\n<\/tr>\n
REPLACE<\/td>\nSearch str_exp1 for occurrences of str_exp2 and replace with str_exp3.
\n{fn LTRIM(str_exp1,str_exp2,str_exp3)}<\/td>\n<\/tr>\n
RIGHT<\/td>\nReturns the rightmost count characters of string_exp. Returns the number of characters requested from the left side of the given string.
\n{fn RIGHT(string_exp,count)}<\/td>\n<\/tr>\n
RTRIM<\/td>\nReturns the characters of string_exp with trailing blanks removed.
\n{fn LTRIM(string_exp)}<\/td>\n<\/tr>\n
SPACE<\/td>\nReturns a character string consisting of count spaces. Returns the number of characters requested from the left side of the given string.
\n{fn SPACE(count)}<\/td>\n<\/tr>\n
SUBSTRING<\/td>\nExtracts one or more characters from a string. Returns the number of characters requested from the left side of the given string.
\n{fn SUBSTRING(string_exp,start,length)}<\/td>\n<\/tr>\n
UCASE<\/td>\nConverts strings to uppercase. Returns the number of characters requested from the left side of the given string.
\n{fn UCASE(string_exp)}<\/td>\n<\/tr>\n
Numeric Functions<\/th>\n<\/tr>\n
ABS<\/td>\nReturns the absolute value of numeric_exp.
\n{fn ABS(numeric_exp)}<\/td>\n<\/tr>\n
CEILING<\/td>\nReturns the smallest integer greater than or equal to numeric_exp. The return value is of the same data type as the input parameter.
\n{fn CEILING(numeric_exp)}<\/td>\n<\/tr>\n
FLOOR<\/td>\nRounds a number down to the nearest (smallest) whole number.
\n{fn FLOOR(numeric_exp)}<\/td>\n<\/tr>\n
MOD<\/td>\nReturns the remainder (modulus) of integer_exp1 divided by integer_exp2.
\n{fn MOD(integer_exp1,integer_exp2)}<\/td>\n<\/tr>\n
ROUND<\/td>\nRounds a number (value1) down to the number of decimal digits specified in value2.
\n{fn ROUND(value1,value2)}<\/td>\n<\/tr>\n
SIGN<\/td>\nReturns a value indicating the sign of the provided value.
\n{fn SIGN(value)}<\/td>\n<\/tr>\n
Date Functions<\/th>\n<\/tr>\n
CURRENT_DATE<\/td>\nReturns the current host system date.
\n{fn CURRENT_DATE( )}<\/td>\n<\/tr>\n
CURDATE<\/td>\nReturns the current host system date.
\n{fn CURDATE( )}<\/td>\n<\/tr>\n
DAYOFMONTH<\/td>\nReturns a number that consists of the Day portion of a given date.
\n{fn DAYOFMONTH(date_exp)}<\/td>\n<\/tr>\n
MONTH<\/td>\nReturns 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.
\n{fn MONTH(date_exp)}<\/td>\n<\/tr>\n
YEAR<\/td>\nReturns 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.
\n{fn YEAR(date_exp)}<\/td>\n<\/tr>\n
Misc Functions<\/th>\n<\/tr>\n
ISNULL<\/td>\nReturns true if the value is NULL.
\n{fn ISNULL(value)}<\/td>\n<\/tr>\n
ISNUMERIC<\/td>\nReturns true if the value represents a number.
\n{fn ISNUMERIC(value)}<\/td>\n<\/tr>\n
COALESCE<\/td>\nReturns the first non-null value from the list provided. Used in joins that can return NULL values.
\n{fn COALESCE(value1,value2)}<\/td>\n<\/tr>\n
DECODE<\/td>\nProvides an IF THEN ELSE structure in the form,
\nif (column==test)then value1 else value2.
\n{fn DECODE(column,test,value1,value2)}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":75,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54],"tags":[],"_links":{"self":[{"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2342"}],"collection":[{"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/users\/75"}],"replies":[{"embeddable":true,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/comments?post=2342"}],"version-history":[{"count":4,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2342\/revisions"}],"predecessor-version":[{"id":6176,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/2342\/revisions\/6176"}],"wp:attachment":[{"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/media?parent=2342"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/categories?post=2342"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/email.minisoft.com\/support\/wp-json\/wp\/v2\/tags?post=2342"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}