Databases Reference
In-Depth Information
Table 11.3
Built-in functions
Function
Description
concat(string
a
, string
b
)
Returns the concatenation of string
a
with string
b
.
substr(string
str
, int
start
)
substr(string
str
, int
start
, int
length
)
Returns the substring of
str
starting at
start
. The result
goes until the end of
str
unless an optional
length
argument is specified.
round(double
num
)
Returns the closest integer (BIGINT).
floor(double
num
)
Returns the largest integer (BIGINT) that's equal to or
smaller than
num
.
ceil(double
num
)
ceiling(double
num
)
Returns the smallest integer (BIGINT) that's equal to or
bigger than
num
.
sqrt(double
num
)
Returns the square root of
num
.
rand()
rand(int
seed
)
Returns a random number (that changes from row to row).
The optional seed value can make the random number
sequence deterministic.
Ln(double
num
)
Returns the natural log of
num
.
log2(double
num
)
Returns the base-2 log of
num
.
log10(double
num
)
Returns the base-10 log of
num
.
Returns the natural log of
num
. Or returns the base-
base
log of
num
.
log(double
num
)
log(double
base
, double
num
)
exp(double
a
)
Raise
e
(the base of natural logarithm) to the power of
a
.
Returns
a
raised to the power of
b
.
power(double
a
, double
b
)
pow(double
a
, double
b
)
upper(string
s
)
ucase(string
s
)
Returns string
s
in uppercase.
lower(string
s
)
lcase(string
s
)
Returns string
s
in lowercase.
trim(string
s
)
Returns string
s
with spaces trimmed on both ends.
ltrim(string
s
)
Returns string
s
with spaces trimmed on the left end.
rtrim(string
s
)
Returns string
s
with spaces trimmed on the right end.
regexp(string
s
, string
regex
)
Returns whether the string
s
matches the Java regular
expression
regex
.
regexp_replace(string
s
, string
regex
, string
replacement
)
Returns a string where all parts of
s
that match the
Java regular expression
regex
are replaced with
replacement
.
day(string
date
)
dayofmonth(string
date
)
Returns the day part of a date or timestamp string.
Returns the month part of a date or timestamp string.
month(string
date
)
year(string
date
)
Returns the year part of a date or timestamp string.
Returns the date part (year-month-day) of a
timestamp string.
To_date(string
timestamp
)
unix_timestamp(string
timestamp
)
Convert a timestamp string to UnixTime.
