The scalar functions are listed by category:JAVA

The JDBC API supports escape syntax for numeric, string, time, date, system, and
conversion functions on scalar values. These scalar functions may be used in SQL
strings as described in Section 13.4.1 “Scalar Functions” on page 13-109. The Open
Group CLI specification provides more information on the semantics of the scalar
functions. The scalar functions are listed below for reference.

If a DBMS supports a scalar function, the driver should also. Because scalar
functions are supported by different DBMSs with slightly different syntax, it is the
driver’s job either to map them into the appropriate syntax or to implement the
functions directly in the driver.

A user should be able to find out which functions are supported by calling metadata
methods. For example, the method DatabaseMetaData.getNumericFunctions
returns a comma separated list of the Open Group CLI names of the numeric
functions supported. Similarly, the method DatabaseMetaData.getStringFunctions returns a list of string functions supported, and so on.



NUMERIC FUNCTIONS

Function Name Function Returns

ABS(number) Absolute value of number
ACOS(float) Arccosine, in radians, of float
ASIN(float) Arcsine, in radians, of float
ATAN(float) Arctangent, in radians, of float
ATAN2(float1, float2) Arctangent, in radians, of float2 / float1
CEILING(number) Smallest integer >= number
COS(float) Cosine of float radians
C-184 JDBC 3.0 • July 2000
COT(float) Cotangent of float radians
DEGREES(number) Degrees in number radians
EXP(float) Exponential function of float
FLOOR(number) Largest integer <= number
LOG(float) Base e logarithm of float
LOG10(float) Base 10 logarithm of float
MOD(integer1, integer2) Remainder for integer1 / integer2
PI() The constant pi
POWER(number, power) number raised to (integer) power
RADIANS(number) Radians in number degrees
RAND(integer) Random floating point for seed integer
ROUND(number, places) number rounded to places places
SIGN(number) -1 to indicate number is < 0;
0 to indicate number is = 0;
1 to indicate number is > 0
SIN(float) Sine of float radians
SQRT(float) Square root of float
TAN(float) Tangent of float radians
TRUNCATE(number, places) number truncated to places places

STRING FUNCTIONS

Function Name Function Returns

ASCII(string) Integer representing the ASCII code value of the leftmost character in string
CHAR(code) Character with ASCII code value code, where code is between 0 and 255
CONCAT(string1, string2) Character string formed by appending string2 to string1; if a string is
null, the result is DBMS-dependent
DIFFERENCE(string1, Integer indicating the difference between the
string2) values returned by the function SOUNDEX for string1 and string2
INSERT(string1, start, A character string formed by deleting length
length, string2) characters from string1 beginning at start, and inserting string2 into
string1 at start
LCASE(string) Converts all uppercase characters in string to lowercase
LEFT(string, count) The count leftmost characters from string
LENGTH(string) Number of characters in string, excluding trailing blanks
LOCATE(string1, Position in string2 of the first occurrence of
string2[, start]) string1, searching from the beginning of string2; if start is specified,
the search begins from position start. 0 is returned if string2 does not contain
string1. Position 1 is the first character in string2.
LTRIM(string) Characters of string with leading blank spaces removed
REPEAT(string, count) A character string formed by repeating string count times
REPLACE(string1, string2, Replaces all occurrences of string2 in string1
string3) with string3
RIGHT(string, count) The count rightmost characters in string
RTRIM(string) The characters of string with no trailing blanks
Appendix C Scalar Functions C-185
SOUNDEX(string) Acharacter string, which is data source-dependent, representing the sound of
the words in string; this could be a four-digit SOUNDEX code, a phonetic
representation of each word, etc.
SPACE(count) A character string consisting of count spaces
SUBSTRING(string, start, A character string formed by extracting length
length) characters from string beginning at start
UCASE(string) Converts all lowercase characters in string to uppercase

TIME and DATE FUNCTIONS

Function Name Function Returns

CURDATE() The current date as a date value
CURTIME() The current local time as a time value
DAYNAME(date) A character string representing the day component of date; the name for the
day is specific to the data source
DAYOFMONTH(date) An integer from 1 to 31 representing the day of the month in date
DAYOFWEEK(date) An integer from 1 to 7 representing the day of the week in date; 1 represents
Sunday
DAYOFYEAR(date) An integer from 1 to 366 representing the day of the year in date
HOUR(time) An integer from 0 to 23 representing the hour component of time
MINUTE(time) An integer from 0 to 59 representing the minute component of time
MONTH(date) An integer from 1 to 12 representing the month component of date
MONTHNAME(date) A character string representing the month component of date; the name for
the month is specific to the data source
NOW() A timestamp value representing the current date and time
QUARTER(date) An integer from 1 to 4 representing the quarter in date; 1 represents January
1 through March 31
SECOND(time) An integer from 0 to 59 representing the second component of time
TIMESTAMPADD(interval, A timestamp calculated by adding count numcount,
timestamp) ber of interval(s) to timestamp; interval may be one of the following:
SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE,
SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH,
SQL_TSI_QUARTER, or SQL_TSI_YEAR
TIMESTAMPDIFF(interval, An integer representing the number of interval
timestamp1, timestamp2) by which timestamp2 is greater than timestamp1; interval may be one of
the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND,
SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK,
SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR
WEEK(date) An integer from 1 to 53 representing the week of the year in date
YEAR(date) An integer representing the year component of date
C-186 JDBC 3.0

SYSTEM FUNCTIONS

Function Name Function Returns

DATABASE() Name of the database
IFNULL(expression, value) value if expression is null;
expression if expression is not null
USER() User name in the DBMS