The Salesforce driver supports a number of functions that you may use in expressions, as listed and described in the tables in this section.

Table 1. Numerical Functions Supported
Numerical Function Description
ABS(d) Returns the absolute value of a double value.
ACOS(d) Returns the arc cosine of an angle.
ASIN(d) Returns the arc sine of an angle.
ATAN(d) Returns the arc tangent of an angle.
ATAN2(a,b) Returns the tangent of a/b.
BITAND(a,b) Returns a and b.
BITOR(a,b) Returns a or b.
CEILING(d) Returns the smallest integer that is not less than d.
COS(d) Returns the cosine of an angle.
COT(d) Returns the cotangent of an angle.
DEGREES(d) Converts radians to degrees.
EXP(d) Returns e (2.718... raised to the power of d).
FLOOR(d) Returns the largest integer that is not greater than d.
LOG(d) Returns the natural logarithm (base e).
LOG10(d) Returns the logarithm (base 10).
MOD(a,b) Returns a modulo b.
PI( ) Returns pi (3.1415...).
POWER(a,b) Returns a raised to the power of b.
RADIANS(d) Converts degrees to radians.
RAND( ) Returns a random number x bigger or equal to 0.0 and smaller than 1.0.
ROUND(a,b) Rounds a to b digits after the decimal point.
ROUNDMAGIC(d) Solves rounding problems such as 3.11-3.1-0.01.
SIGN(d) Returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0.
SIN(d) Returns the sine of an angle.
SQRT(d) Returns the square root.
TAN(A) Returns the trigonometric tangent of an angle.
TRUNCATE(a,b) Truncates a to b digits after the decimal point.
Table 2. String Functions Supported
String Function Description
ASCII(s) Returns the ASCII code of the leftmost character of s.
BIT_LENGTH(str) Returns the length of the string in bits.
CHAR(c) Returns a character that has the ASCII code c.
CHAR_LENGTH(str) Returns the length of the string in characters.
CONCAT(str1,str2) Returns the string that results from concatenating str1 + str2.
DIFFERENCE(s1,s2) Returns the difference between the sound of s1 and s2.
HEXTORAW(s1) Returns a translated string/.
INSERT(s,start,len,s2) Returns a string where len number of characters beginning at start has been replaced by s2.
LCASE(s) Converts s to lower case.
LEFT(s,count) Returns the leftmost count of characters of s. If s requires double quoting, use SUBSTRING( ) instead.
LENGTH(s) Returns the number of characters in s.
LOCATE(search,s,[start]) Returns the first index (1=left, 0=not found) where search is found in s, starting at start.
LTRIM(s) Removes all leading blanks in s.
OCTET_LENGTH(str) Returns the length of the string in bytes (twice the number of characters).
RAWTOHEX(s1) Returns translated string.
REPEAT(s,count) Returns s repeated count times.
REPLACE(s,replace,s2) Returns s with all occurrences of replace replaced with s2.
RIGHT(s,count) Returns the right-most count of characters of s.
RTRIM(s) Removes all trailing spaces in s.
SOUNDEX(s) Returns a 4-character code representing the sound of s.
SPACE(count) Returns a string consisting of count spaces.
SUBSTR(s,start[,len]) Alias for substring.
SUBSTRING(s,start[,len]) Returns the substring starting at start (1=left) with length len.
UCASE(s) Converts s to uppercase.
LOWER(s) Converts s to lowercase.
UPPER(s) Converts s to uppercase.
Table 3. Date/Time Functions Supported
Date/Time Function Description
CURDATE( ) Returns the current date.
CURTIME( ) Returns the current time.
DATEDIFF(string, datetime1, datetime2) Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values:
  • 'ms'='millisecond'
  • 'ss'='second'
  • 'mi'='minute'
  • 'hh'='hour'
  • 'dd'='day'
  • 'mm'='month'
  • 'yy' = 'year'
Both the long and short form of the strings can be used.
DAYNAME(date) Returns the name of the day.
DAYOFMONTH(date) Returns the day of the month (1-31).
DAYOFWEEK(date) Returns the day of the week (1 means Sunday).
DAYOFYEAR(date Returns the day of the year (1-366).
HOUR(time) Returns the hour (0-23).
MINUTE(time) Returns the minute (0-59).
MONTH(date) Returns the month (1-12).
MONTHNAME(date) Returns the name of the month.
NOW( ) Returns the current date and time as a timestamp.
QUARTER(date) Returns the quarter (1-4).
SECOND(time) Returns the second (0-59).
WEEK(date) Returns the week of this year (1-53).
YEAR(date) Returns the year.
CURRENT_DATE Returns the current date.
CURRENT_TIME Returns the current time.
CURRENT_TIMESTAMP Returns the current timestamp.
Table 4. System/Connection Functions Supported
System/Connection Function Description
DATABASE( ) Returns the name of the database of this connection.
USER( ) Returns the user name of this connection.
CURRENT_USER SQL standard function, returns the user name of this connection.
CURSESSIONID( ) Returns the ID of the session (connection) on which this function was invoked.
IDENTITY( ) Returns the last identity value that was inserted by this connection.
Table 5. System Functions Supported
System Function Description
IFNULL(expr,value) If expr is NULL, then value is returned; otherwise the result of expr is returned. See COALESCE(expr1, expr2, ...) in this table for evaluating multiple expressions.
CONVERT(term,type) Converts term to another data type.
CAST(term AS type) Converts term to another data type.
COALESCE(expr1,expr2, ...) If expr1 is not Null, then it is returned; otherwise, expr2 is evaluated and, if not Null, it is returned, and so on. This is an ANSISQL standard system function.
NULLIF(value1,value2) If value1 equals value2, then Null is returned; otherwise, value1 is returned.
CASE value1 WHEN value2 THEN value3 [ELSE value4] END When value1 equals value2, then value3 is returned; otherwise, value4 or Null is returned in the absence of ELSE.
CASE WHEN expr1 THEN value1 [WHEN expr2 THEN value2] [ELSE value4] END When expr1 is true, then value1 is returned (optionally repeated for more cases); otherwise value4 or Null is returned in the absence of ELSE.
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE| SECOND} FROM datetime_value) Any of the date and time terms can be extracted from datetime_value.
POSITION(string_expression1 IN string_expression2) If string_expression1 is a sub-string of string_expression2, then the position of the sub-string, counting from one, is returned; otherwise, 0 is returned.
SUBSTRING(string_expression FROM numeric_expression1 [FOR numeric_expression2]) string_expression is returned from the numeric_expression1 starting location. Optionally, numeric_expression2 specifies the length of the substring.
TRIM([{LEADING | TRAILING | BOTH}] FROM string_expression) When returned, either the leading or trailing spaces, or both, are trimmed from string_expression.