|
JDBC escape syntax for fn keyword
You can specify functions in JDBC escape syntax by using the
fn keyword.
Syntax
{fn functionCall}
where functionCall is the name of one of the scalar functions listed
below. The functions are of the following types:
- Numeric functions
- String functions
- Date and time functions
- System function
Numeric functions
- abs
- Returns the absolute value of a number.
abs(NumericExpression)
The JDBC escape syntax {fn abs(NumericExpression)} is
equivalent to the built-in syntax ABS(NumericExpression).
For more information, see ABS or ABSVAL function.
- acos
- Returns the arc cosine of a specified number.
acos(number)
The JDBC escape syntax {fn acos(number)} is
equivalent to the built-in syntax ACOS(number). For more
information, see ACOS function.
- asin
- Returns the arc sine of a specified number.
asin(number)
The JDBC escape syntax {fn asin(number)} is
equivalent to the built-in syntax ASIN(number). For more
information, see ASIN function.
- atan
- Returns the arc tangent of a specified number.
atan(number)
The JDBC escape syntax {fn atan(number)} is
equivalent to the built-in syntax ATAN(number). For more
information, see ATAN function.
- atan2
- Returns the arc tangent in radians of y/x.
atan2(y, x)
The JDBC escape syntax {fn atan2(y, x)} is
equivalent to the built-in syntax ATAN2(y, x).
For more information, see ATAN2 function.
- ceiling
- Rounds the specified number up, and returns the smallest number that is
greater than or equal to the specified number.
ceiling(number)
The JDBC escape syntax {fn ceiling(number)} is
equivalent to the built-in syntax CEILING(number). For
more information, see CEIL or CEILING function.
- cos
- Returns the cosine of a specified number.
cos(number)
The JDBC escape syntax {fn cos(number)} is equivalent
to the built-in syntax COS(number). For more
information, see COS function.
- cot
- Returns the cotangent of a specified number.
cot(number)
The JDBC escape syntax {fn cot(number)} is equivalent
to the built-in syntax COT(number). For more
information, see COT function.
- degrees
- Converts a specified number from radians to degrees.
degrees(number)
The JDBC escape syntax {fn degrees(number)} is
equivalent to the built-in syntax DEGREES(number). For
more information, see DEGREES function.
- exp
- Returns e raised to the power of the specified number.
exp(number)
The JDBC escape syntax {fn exp(number)} is equivalent
to the built-in syntax EXP(number). For more
information, see EXP function.
- floor
- Rounds the specified number down, and returns the largest number that
is less than or equal to the specified number.
floor(number)
The JDBC escape syntax {fn floor(number)} is
equivalent to the built-in syntax FLOOR(number). For
more information, see FLOOR function.
- log
- Returns the natural logarithm (base e) of the specified number.
log(number)
The JDBC escape syntax {fn log(number)} is equivalent
to the built-in syntax LOG(number). For more
information, see LN or LOG function.
- log10
- Returns the base-10 logarithm of the specified number.
log10(number)
The JDBC escape syntax {fn log10(number)} is
equivalent to the built-in syntax LOG10(number). For
more information, see LOG10 function.
- mod
- Returns the remainder (modulus) of argument 1 divided by argument 2.
The result is negative only if argument 1 is negative.
mod(integer_type, integer_type)
The JDBC escape syntax
{fn mod(integer_type, integer_type)} is
equivalent to the built-in syntax
MOD(integer_type, integer_type). For more
information, see MOD function.
- pi
- Returns a value that is closer than any other value to pi.
pi()
The JDBC escape syntax {fn pi()} is equivalent to the
built-in syntax PI(). For more information, see
PI function.
- radians
- Converts a specified number from degrees to radians.
radians(number)
The JDBC escape syntax {fn radians(number)} is
equivalent to the built-in syntax RADIANS(number). For
more information, see RADIANS function.
- rand
- Returns a random number given a seed number.
rand(seed)
The JDBC escape syntax {fn rand(seed)} is equivalent
to the built-in syntax RAND(seed). For more
information, see RAND function.
- sign
- Returns an integer that represents the sign of a specified number (+1 if the
number is positive, -1 if it is negative, 0 if it is 0).
sign(number)
The JDBC escape syntax {fn sign(number)} is equivalent
to the built-in syntax SIGN(number). For more
information, see SIGN function.
- sin
- Returns the sine of a specified number.
sin(number)
The JDBC escape syntax {fn sin(number)} is equivalent
to the built-in syntax SIN(number). For more
information, see SIN function.
- sqrt
- Returns the square root of a floating-point number.
sqrt(FloatingPointExpression)
The JDBC escape syntax
{fn sqrt(FloatingPointExpression)} is equivalent to the
built-in syntax SQRT(FloatingPointExpression). For more
information, see SQRT function.
- tan
- Returns the tangent of a specified number.
tan(number)
The JDBC escape syntax {fn tan(number)} is equivalent
to the built-in syntax TAN(number). For more
information, see TAN function.
String functions
- concat
- Returns the concatenation of character strings; that is, the character
string formed by appending the second string to the first string. If either
string is null, the result is NULL.
concat(CharacterExpression, CharacterExpression)
The JDBC escape syntax
{fn concat(CharacterExpression,
CharacterExpression)} is equivalent to the built-in syntax
CharacterExpression || CharacterExpression. For more
information, see Concatenation operator.
- lcase
- Returns a string in which all alphabetic characters in the argument have
been converted to lowercase.
lcase(CharacterExpression)
The JDBC escape syntax
{fn lcase(CharacterExpression)} is equivalent to the
built-in syntax LCASE(CharacterExpression). For more
information, see LCASE or LOWER function.
- length
- Returns the number of characters in a character string expression.
length(CharacterExpression)
The JDBC escape syntax
{fn length(CharacterExpression)} is equivalent to the
built-in syntax LENGTH(CharacterExpression). For more
information, see LENGTH function.
- locate
- Returns the position in the second CharacterExpression of the first
occurrence of the first CharacterExpression. Searches from the beginning
of the second CharacterExpression, unless the startIndex parameter
is specified.
locate(CharacterExpression,CharacterExpression [, startIndex] )
The JDBC escape syntax
{fn locate(CharacterExpression, CharacterExpression
[, startIndex] )} is equivalent to the built-in syntax
LOCATE(CharacterExpression, CharacterExpression
[, StartPosition] ). For more information, see
LOCATE function.
- ltrim
- Removes blanks from the beginning of a character string expression.
ltrim(CharacterExpression)
The JDBC escape syntax
{fn ltrim(CharacterExpression)} is equivalent to the
built-in syntax LTRIM(CharacterExpression). For more
information, see LTRIM function.
- rtrim
- Removes blanks from the end of a character string expression.
rtrim(CharacterExpression)
The JDBC escape syntax
{fn rtrim(CharacterExpression)} is equivalent to the
built-in syntax RTRIM(CharacterExpression). For more
information, see RTRIM function.
- substring
- Forms a character string by extracting length characters from the
CharacterExpression beginning at startIndex. The index of the
first character in the CharacterExpression is 1.
substring(CharacterExpression, startIndex, length)
The JDBC escape syntax
{fn substring(CharacterExpression, startIndex,
length)} is equivalent to the built-in syntax
SUBSTR(CharacterExpression, startIndex,
length). For more information, see
SUBSTR function.
- ucase
- Returns a string in which all alphabetic characters in the argument have
been converted to uppercase.
ucase(CharacterExpression)
The JDBC escape syntax
{fn ucase(CharacterExpression)} is equivalent to the
built-in syntax UCASE(CharacterExpression). For more
information, see UCASE or UPPER function.
Date and time functions
- curdate
- Returns the current date.
curdate()
The JDBC escape syntax {fn curdate()} is equivalent to the
built-in syntax CURRENT_DATE. For more information, see
CURRENT_DATE function.
- curtime
- Returns the current time.
curtime()
The JDBC escape syntax {fn curtime()} is equivalent to the
built-in syntax CURRENT_TIME. For more information, see
CURRENT_TIME function.
- hour
- Returns the hour part of a time value.
hour(expression)
The JDBC escape syntax {fn hour(expression)} is
equivalent to the built-in syntax HOUR(expression). For
more information, see HOUR function.
- minute
- Returns the minute part of a time value.
minute(expression)
The JDBC escape syntax {fn minute(expression)} is
equivalent to the built-in syntax MINUTE(expression).
For more information, see
MINUTE function.
- month
- Returns the month part of a date value.
month(expression)
The JDBC escape syntax {fn month(expression)} is
equivalent to the built-in syntax MONTH(expression).
For more information, see
MONTH function.
- second
- Returns the seconds part of a time value.
second(expression)
The JDBC escape syntax {fn second(expression)} is
equivalent to the built-in syntax SECOND(expression).
For more information, see
SECOND function.
- TIMESTAMPADD
- Use the TIMESTAMPADD function to add the value of an
interval to a timestamp. The function applies the integer to the specified
timestamp based on the interval type and returns the sum as a new timestamp.
You can subtract from the timestamp by using negative integers.
TIMESTAMPADD is a JDBC escaped function and is accessible
only by using the JDBC escape function syntax.
TIMESTAMPADD( interval, integerExpression, timestampExpression )
To perform TIMESTAMPADD on dates and times, it is necessary
to convert the dates and times to timestamps. Dates are converted to timestamps
by putting 00:00:00.0 in the time-of-day fields. Times are converted to
timestamps by putting the current date in the date fields.
Do not put a datetime column inside a timestamp arithmetic function in WHERE
clauses, because the optimizer will not use any index on the column.
- TIMESTAMPDIFF
- Use the TIMESTAMPDIFF function to find the difference
between two timestamp values at a specified interval. For example, the function
can return the number of minutes between two specified timestamps.
The TIMESTAMPDIFF is a JDBC escaped function and is
accessible only by using the JDBC escape function syntax.
TIMESTAMPDIFF( interval, timestampExpression1, timestampExpression2 )
To perform TIMESTAMPDIFF on dates and times, it is necessary
to convert the dates and times to timestamps. Dates are converted to timestamps
by putting 00:00:00.0 in the time-of-day fields. Times are converted to
timestamps by putting the current date in the date fields.
Do not put a datetime column inside a timestamp arithmetic function in WHERE
clauses, because the optimizer will not use any index on the column.
- year
- Returns the year part of a date value.
year(expression)
The JDBC escape syntax {fn year(expression)} is
equivalent to the built-in syntax YEAR(expression).
For more information, see
YEAR function.
Valid intervals for TIMESTAMPADD and TIMESTAMPDIFF
The TIMESTAMPADD and TIMESTAMPDIFF functions
are used to perform arithmetic with timestamps. These two functions use the
following valid intervals for arithmetic operations:
- SQL_TSI_DAY
- SQL_TSI_FRAC_SECOND
- SQL_TSI_HOUR
- SQL_TSI_MINUTE
- SQL_TSI_MONTH
- SQL_TSI_QUARTER
- SQL_TSI_SECOND
- SQL_TSI_WEEK
- SQL_TSI_YEAR
Examples for the TIMESTAMPADD and TIMESTAMPDIFF escape functions
To return a timestamp value one month later than the current timestamp, use
the following syntax:
{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}
To return the number of weeks between now and the specified time on January
1, 2008, use the following syntax:
{fn TIMESTAMPDIFF(SQL_TSI_WEEK, CURRENT_TIMESTAMP,
timestamp('2008-01-01-12.00.00.000000'))}
System function
- user
- Returns the authorization identifier or name of the current user. If there
is no current user, it returns APP.
user()
The JDBC escape syntax {fn user()} is equivalent to the
built-in syntax USER. For more information, see
USER function.
|
