EJB query: Scalar functions

EJB query contains scalar built-in functions for doing type conversions, string manipulation, and for manipulating date-time values.

Details of EJB query scalar built-in functions follow:

Numeric functions

ABS ( < any numeric datatype > ) -> < any numeric datatype >

MOD ( <int>, <int> ) -> int

SQRT ( < any numeric datatype > ) -> Double

Type conversion functions

CHAR ( < any numeric datatype > ) -> string CHAR ( < string > ) -> string CHAR ( < any datetime datatype > [, Keyword k ]) -> string

Datetime datatype is converted to its string representation in a format specified by the keyword k. The valid keywords values are ISO, USA, EUR or JIS. If k is not specified the default is ISO.

BIGINT ( < any numeric datatype > ) -> Long
BIGINT ( < string > ) -> Long

The function in the second line of the following code converts the argument to an integer n by truncation, and returns the date that is n-1 days after January 1, 0001:

DATE ( < date string > ) -> Date
DATE (  < any numeric datatype>) -> Date

The following function returns date portion of a timestamp:

DATE( timestamp ) -> Date DATE ( < timestamp-string > ) -> Date

The following function converts number to decimal with optional precision p and scale s.

DECIMAL ( < any numeric datatype > [, p [ ,s ] ] ) -> Decimal

The following function converts string to decimal with optional precision p and scale s.

DECIMAL ( < string > [ , p [ , s ] ] ) -> Decimal

DOUBLE ( < any numeric datatype > ) -> Double
DOUBLE ( < string > ) -> Double

FLOAT ( < any numeric datatype > ) -> Double
FLOAT ( < string > ) -> Double

Float is a synonym for DOUBLE.

INTEGER ( < any numeric datatype > ) -> Integer
INTEGER ( < string > ) -> Integer

REAL ( < any numeric datatype > ) -> Float

SMALLINT ( < any numeric datatype ) -> Short SMALLINT ( < string > ) -> Short

TIME ( < time > ) -> Time
TIME ( < time-string  > ) -> Time
TIME ( < timestamp > ) -> Time
TIME ( < timestamp-string  > ) -> Time

TIMESTAMP ( < timestamp > ) -> Timestamp
TIMESTAMP ( < timestamp-string > ) -> Timestamp

String functions

CONCAT ( <string>, <string> ) -> String

The following function returns a character string representing absolute value of the argument not including its sign or decimal point. For example, digits( -42.35) is "4235".

DIGITS ( Decimal d ) -> String

The following function returns the length of the argument in bytes. If the argument is a numeric or datetime type, it returns the length of internal representation.

LENGTH ( < string > ) -> Integer

The following function returns a copy of the argument string where all upper case characters have been converted to lower case.

LCASE ( < string > ) -> String

The following function returns the starting position of the first occurrence of argument 1 inside argument 2 with optional start position. If not found, it returns 0.

LOCATE ( String s1 , String s2 [, Integer start ] ) -> Integer

The following function returns a substring of s beginning at character m and containing n characters. If n is omitted, the substring contains the remainder of string s. The result string is padded with blanks if needed to make a string of length n.

SUBSTRING ( String s , Integer m [ , Integer n ] ) -> String

The following function returns a copy of the argument string where all lower case characters have been converted to upper case.

UCASE ( < string > ) -> String

Date - time functions

The following function returns the day portion of its argument. For a duration, the return value can be -99 to 99.

DAY (  Date ) -> Integer
DAY ( < date-string > ) -> Integer
DAY ( < date-duration > ) -> Integer
DAY ( Timestamp  ) -> Integer
DAY ( < timestamp-string > ) -> Integer
DAY ( < timestamp-duration > ) -> Integer

The following function returns one more than number of days from January 1, 0001 to its argument.

DAYS ( Date  ) -> Integer
DAYS ( < Date-string > ) -> Integer
DAYS ( Timestamp  ) -> Integer
DAYS ( < timestamp-string > ) -> Integer

The following function returns the hour part of its argument. For a duration, the return value can be -99 to 99.

HOUR ( Time ) -> Integer
HOUR ( < time-string > ) -> Integer
HOUR ( < time-duration > ) -> Integer
HOUR ( Timestamp ) -> Integer
HOUR ( < timestamp-string > ) -> Integer
HOUR ( < timestamp-duration > ) -> Integer

The following function returns the microsecond part of its argument.

MICROSECOND ( Timestamp ) -> Integer
MICROSECOND ( < timestamp-string > ) -> Integer
MICROSECOND ( < timestamp-duration > ) -> Integer

The following function returns the minute part of its argument. For a duration, the return value can be -99 to 99.

MINUTE ( Time ) -> Integer
MINUTE ( < time-string > ) -> Integer
MINUTE ( < time-duration > ) -> Integer
MINUTE ( Timestamp ) -> Integer
MINUTE ( < timestamp-string > ) -> Integer
MINUTE ( < timestamp-duration > ) -> Integer

The following function returns the month portion of its argument. For a duration, the return value can be -99 to 99.

MONTH (  Date ) -> Integer
MONTH ( < date-string > ) -> Integer
MONTH ( < date-duration > ) -> Integer
MONTH ( Timestamp  ) -> Integer
MONTH ( < timestamp-string > ) -> Integer
MONTH ( < timestamp-duration > ) -> Integer

The following function returns the second part of its argument. For a duration, the return value can be -99 to 99.

SECOND ( Time ) -> Integer
SECOND ( < time-string > ) -> Integer
SECOND ( < time-duration > ) -> Integer
SECOND ( Timestamp ) -> Integer
SECOND ( < timestamp-string > ) -> Integer
SECOND ( < timestamp-duration > ) -> Integer

The following function returns the year portion of its argument. For a duration, the return value can be -9999 to 9999.

YEAR (  Date ) -> Integer
YEAR ( < date-string > ) -> Integer
YEAR ( < date-duration > ) -> Integer
YEAR ( Timestamp  ) -> Integer
YEAR ( < timestamp-string > ) -> Integer
YEAR ( < timestamp-duration > ) -> Integer

Related concepts

Scalar functions

EJB query language

Related tasks

Use EJB query